extract multiple pieces of text between two strings

**verbatim** · 02-11-2014, 10:48 AM

Hi,

Can someone help me please?

In column E of row 1 I have one column with a long line of text containing information in that should be separated into cells for the
following fields:

Year, make, model, drive, part, name, phone, text, email, location, notes, stream, date/time, disclaimer

Here is an example of the text in column E:

Year: 2001 Make: Nasa Model: big spaceship Drive: Automatic 2 Wheel Drive Spaceship: 4 12.7 Liter \----------------------------- Part: Hood \----------------------------- Name: Malcolm Jaegger Phone: 3195553361 Text: Yes Email: [email protected] Location: Chicago, Louisiana 71527 Notes: Thank you for using our excel forum! thesmartestpeopleever.com Team Stream: \-------------------------------------- Date/Time:February 7, 2014, 1:28 PM By reading this email you agree to the Terms and Condtions set forth by thesmartestpeopleever.com. You agree not to eat, drink, reproduce or alter this information for bitcoins to leprechauns.

The data in each field can be different lengths, and I would like to take pieces of text out of Column E, and put it in other columns in the row.

Each field has its own column, and I would like to copy the data for that field from the column containing all data to its respective column.

=MID(E1,SEARCH("Year:",E1)+5,SEARCH("Make:",E1)-SEARCH("object",E1)+5)

The formula above was inspired by an "extract text between two strings" post, but will not work.

I tried to make a formula that would extract everything between the strings "Notes:" and "Team Stream:" or for example, so I could get the result "Thank you for using our excel forum! thesmartestpeopleever.com" or "Year" and "Make" so I could get the result "2001" if using the example above. I was not able to do it.

I have attached a sample photo and an excel file.

How do I separate/extract each columns data to the appropriate column?

I believe you can do this with both formulas and vba functions. If you can give me any help with the formula I would greatly appreciate it. Thanks in advance,

Verb

Edit: after I posted this question here I also posted it at http://www.mrexcel.com/forum/excel-q...ml#post3714269

Thanks again

**verbatim** · 02-11-2014, 03:13 PM

i was not aware of this. the request has been fulfilled

thanks

**verbatim** · 02-11-2014, 04:42 PM

=TRIM(LEFT(SUBSTITUTE(MID(D1,FIND("|",SUBSTITUTE(D1,"_","|",3))+1, LEN(D1)),"_",REPT(" ",LEN(D1))),LEN(D1)))

the formula above returns the phone number, but it also returns a space and the word "Text" 3195553361 Text. I have figured out how to resolve that and i'm now good to go. Thanks for all of your help.