Extracting text separated with a character

[Tkeller]

I have a very large column of data that consistes of either 1, 2, or 3 phases, with each phase separated with the character "|". I would like to extract each phase and place into a separate column, as shown below (so take the label and create the Phase 1, Phase 2, and Phase 3 for each):

Label	Phase 1	Phase 2	Phase 3
option xyz | options 345	option xyz	options 345
optoin xyz | options def |option rds	option xyz	options def	option rds
Single option 123	single option 123

I am struggling with how to extract each phase from the lebel. Again, there are casses where there is 1, 2, or 3 phases.

Thanks,

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Tkeller]

Thank you. I updated this.

 

[Fluff]

Thanks for that
How about

Fluff.xlsm
	A	B	C	D
1	Label	Phase 1	Phase 2	Phase 3
2	option xyz | options 345	option xyz	options 345
3	optoin xyz | options def |option rds	optoin xyz	options def	option rds
4	Single option 123	Single option 123
Main

Cell Formulas
Range	Formula
B2:C2,B4,B3:D3	B2	=TRIM(TEXTSPLIT(A2,"|"))
Dynamic array formulas.

 

[Tkeller]

Wow. That is pretty cool. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.