Need formula to perform text to column function

[wdeontray]

Hi,

I need a formula to perform the same actions as text to column. I need the phrase ONE_TWO_THREE to be split into 3 columns such as ONE | TWO | THREE. I am assuming I will need a function for each column. Any help will be GREATLY appreciated.

 

[Joe4]

Welcome to the Board!

Here is one way, for an entry in cell A1:

First word: =LEFT(A1,FIND("_",A1)-1)
Second word: =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",255)),255,255))
Third word: =TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",255)),255))

 

[jtakw]

Hi,

Here's another way (single formula), formula copied across as far as needed:

Book1
	A	B	C	D	E
1	ONE_TWO_THREE	ONE	TWO	THREE
2	ONE_TWO_THREE_FOUR	ONE	TWO	THREE	FOUR
Sheet317

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE("_"&$A1,"_",REPT(" ",255)),COLUMNS($B1:B1)*255,255))