+ Reply to Thread
Results 1 to 8 of 8

Extract Variable Length Text String

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    Crandall, Texas
    MS-Off Ver
    Excel 2007
    Posts
    15

    Question Extract Variable Length Text String

    Hello, I need to extract a text string from the end of a cell, but the string will never be consistent! I have a macro that extracts email content from Outlook and inserts it into a spreadsheet the results look like this(contents of C1):

    "
    11/05/2012 07:30

    Communication Delays Detected

    Site Last Communication
    ----------------------- ------------------
    SiteName 11/05/2012 06:04
    "

    The only thing consistent is that it will always follow the dashes and it will always precede the date/time as the last characters in the cell! The SiteName can be anywhere from 3 characters to 20.

    I just need a formula to extract only the SiteName into a cell by itself.

    Thanks in advance!

  2. #2
    Registered User
    Join Date
    11-05-2012
    Location
    Palmer, Alaska
    MS-Off Ver
    Office for Mac, Windows 7 & Windows 7 Parallels
    Posts
    17

    Re: Extract Variable Length Text String

    Awesome. Other than within the series of dashes, is a "- " (hyphen+space) ever likely to occur? I'm thinking about a combo of MID(), FIND(), and LEN() functions, but they would only work if there was at least one consistent character or character combo in the string. "- " seems to occur twice, once in the middle of the dashes and once at the end. Is this also an inconsistency?

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Extract Variable Length Text String

    This works for your example:

    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(C1,FIND("-",C1),LEN(C1)),MID(C1,LEN(C1)-19,20),""),"-",""),CHAR(10),""))

    I suspect there may be a better way.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    10-30-2012
    Location
    Crandall, Texas
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Extract Variable Length Text String

    Justin, that should only occur the two times you mentioned. There is the string"- -"(hyphen,space,hyphen) that only occurs once if that helps!

    TMShucks, Your formula worked exactly as I requested, but I just noticed another inconsistency in the way the data appears in the cells after being imported through the macro. I am trying to attach a sample workbook to show the problem(forgive me, new to the forum an trying to figure out how to attach). Some cells are original emails, and others are replies to the original emails and that adds another level of complexity to the formula!

    Figured it out, here's the attachment!

    Thanks for the quick replies!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-05-2012
    Location
    Palmer, Alaska
    MS-Off Ver
    Office for Mac, Windows 7 & Windows 7 Parallels
    Posts
    17

    Re: Extract Variable Length Text String

    This is yucky, but it does the job:
    =RIGHT(RIGHT(C1,LEN(C1)-FIND("- -",C1)),LEN(RIGHT(C1,LEN(C1)-FIND("- -",C1)))-FIND("- ",RIGHT(C1,LEN(C1)-FIND("- -",C1)))-4)

    This takes into account an inconsistant quantity of hyphens that always include "- -" in the series and "- " at the end of the series. The -4 at the end is, I think, because I didn't allow for starting numbers in the FIND() functions. Anybody want to validate that? I'm working on cleaning this one up a little more.
    Last edited by #Justin!; 11-12-2012 at 05:58 PM.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Extract Variable Length Text String

    Maybe:

    =TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(MID(C1,FIND("-",C1),LEN(C1)),"-",""),FIND("/",SUBSTITUTE(MID(C1,FIND("-",C1),LEN(C1)),"-",""))-3),CHAR(10),""),CHAR(13),""))


    Regards, TMS

  7. #7
    Registered User
    Join Date
    10-30-2012
    Location
    Crandall, Texas
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Extract Variable Length Text String

    Justin, this did bring back the SiteName and the date. I could then do a LEFT function in the next column and extract it that way!

    TMS, looks like the Trim formula did the trick! It brought back only the SiteName.

    WOW, I would have never figured that out. Thank you both for your quick replies!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Extract Variable Length Text String

    You're welcome.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1