Trying to find character position to set starting point

[rizzo93]

I'm trying to parse out pieces of text from a larger text string. I'm using the "^" to identify where the pieces begin. There are three instances of this "^".

I can successfully extract the text after the first "^", but having trouble creating new starting points for the next two instances of "^".

My formula looks like this:

=LEFT(MID($C$1,FIND("^",$C$1)+1,LEN($C$1)),FIND("^",MID($C$1,FIND("^",$C$1)+1,LEN($C$1)))-2)

$C$1 is equal to:

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. ^Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. ^Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. ^Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

Using the formula above, I can put this text into one cell:

Cell 1
Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.

But now I want to extract these two strings, each in their own cells:

Cell 2
Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Cell 3
Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

Your suggestions please?

 

[Oeldere]

Use data => text to column and use ^ as the seperator.

 

[AlKey]

Or use formula
With text string in A1:
Enter formula in A2 and copy down

[B]=TRIM(MID(SUBSTITUTE("^"&$A$1,"^",REPT(" ",LEN($A$1))),LEN($A$1)*ROWS($A$1:A1),LEN($A$1)))[/B]

Excel 2016 (Windows) 32 bit
	A
1	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. ^Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. ^Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. ^Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
2	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
3	Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.
4	Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
5	Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Sheet12

 

[rizzo93]

Wow! Both are excellent solutions! Thanks Oeldere and AlKey.

 

[Oeldere]

you are welcome.

Glad I could help.