Extract Variable Length Text String

**sshot1** · 11-12-2012, 04:55 PM

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!

**#Justin!** · 11-12-2012, 05:16 PM

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?

**TMS** · 11-12-2012, 05:16 PM

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

**sshot1** · 11-12-2012, 05:31 PM

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!

**#Justin!** · 11-12-2012, 05:54 PM

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.

**TMS** · 11-12-2012, 05:56 PM

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

**sshot1** · 11-12-2012, 06:05 PM

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!

**TMS** · 11-12-2012, 06:21 PM

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