extract-multiple-pieces-of-text-between-two-strings

[verbatim]

Hi,


Can someone help me please?


In column A 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 A:


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: malcolmjaegger@hotmale.com 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 A, 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(A1,SEARCH("Year:",A1)+5,SEARCH("Make:",A1)-SEARCH("object",A1)+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.



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,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Main data[/TD]
[TD]Year[/TD]
[TD]Make[/TD]
[TD]Email[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]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: malcolmjaegger@hotmale.com 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.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[verbatim]

Edit: I have also posted this question at extract multiple pieces of text between two strings

Thanks

 

[verbatim]

=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.

 

[Rick Rothstein]

Here is an example of the text in column A:

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: malcolmjaegger@hotmale.com 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.

Is there any chance that the lack of a space after the red highlighted text was a typo? If you had a consistent colon/space delimiter, so I could distinguish the colon after "Date/Time" from the colon n the time value (1:28 PM), that would be helpful.

 

[verbatim]

Is there any chance that the lack of a space after the red highlighted text was a typo? If you had a consistent colon/space delimiter, so I could distinguish the colon after "Date/Time" from the colon n the time value (1:28 PM), that would be helpful.

i wish it were a typo but unfortunately that is the way the data looks when i receive it. I may delete that column altogether so if you can come up with something quick by ignoring that column please do so. I would love to hear any creative ideas the community can come up with.

thanks for the reply also.

 

[István Hirsch]

Try this formula as a first approach. Write into B1=Year, C1=Make, D1=Model etc in the order they appear in the string, up to Disclaimer, then put in the cell following Disclaimer a #. The first string to process is in A2, this string should contain "Disclaimer:" (without quotes) (the sample you gave does not). Enter into B2:


=TRIM(MID($A2,FIND(B$1,$A2&"#")+LEN(B$1)+1,FIND(C$1,$A2&"#")-FIND(B$1,$A2&"#")-LEN(B$1)-1))

then drag it down and right.