Text to columns with formula

[addepallibabu]

Hi Everyone,

Can someone please help me out for text to column with formula.

[TABLE="width: 300"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Sachin Kumar Tendulkar *02-02-017*\15000[/TD]
[/TR]
[TR]
[TD]Mahendra Singh Dhoni \*03-02-2017*\40000[/TD]
[/TR]
[TR]
[TD]Virat Kohli *04-02-2017*\65000[/TD]
[/TR]
[TR]
[TD]Ajinkya Rahane Kumar *05-02-2017*\90000[/TD]
[/TR]
[TR]
[TD]Shikhar Dhawan *06-02-2017*\11500[/TD]
[/TR]
[TR]
[TD]Rohit Sharma *07-02-2017*\14000

Example: [TABLE="width: 442"]
<tbody>[TR]
[TD="class: xl65, width: 73"]Sachin[/TD]
[TD="class: xl65, width: 83"]Kumar[/TD]
[TD="class: xl65, width: 69"]Tendulkar[/TD]
[TD="class: xl66, width: 87"]2/2/2017[/TD]
[TD="class: xl65, width: 130"]15000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ingolf]

Hi,

It's not necessary formula. Use Text to Columns first with " " (blank) as separator then use again Text to Columns in last column with this separator ""

 

[addepallibabu]

But I required with formula. It is one of the interview question that I faced.

 

[Peter_SSs]

Since your data is not of consistent format (eg some names have 3 words and some 2 words, sometimes the date part is preceded by "*" and sometimes by "\*") it would have been preferable to see all the expected outcomes and layout.

However, see if these formulas, copied down, do what you expect.

Excel 2010 32 bit
	A	B	C	D	E	F
1	Sachin Kumar Tendulkar *02-02-2017*\15000	Sachin	Kumar	Tendulkar	02-02-2017	15000
2	Mahendra Singh Dhoni \*03-02-2017*\40000	Mahendra	Singh	Dhoni	03-02-2017	40000
3	Virat Kohli *04-02-2017*\65000	Virat		Kohli	04-02-2017	65000
4	Ajinkya Rahane Kumar *05-02-2017*\90000	Ajinkya	Rahane	Kumar	05-02-2017	90000
5	Shikhar Dhawan *06-02-2017*\11500	Shikhar		Dhawan	06-02-2017	11500
6	Rohit Sharma *07-02-2017*\14000	Rohit		Sharma	07-02-2017	14000
TTC

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1)-1)
C1	=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=3,SUBSTITUTE(LEFT(A1,FIND(" ",A1,LEN(B1)+2)-1),B1&" ","",1),"")
D1	=TRIM(MID(LEFT(A1,FIND(" ",A1,LEN(B1&C1)+3)-1),LEN(B1&C1)+2,LEN(A1)))
E1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"*",REPT(" ",20)),40),20))
F1	=RIGHT(SUBSTITUTE(A1,"\",REPT(" ",20)),20)+0

 

[Ingolf]

it's a typo in this formula:
[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]=RIGHT(SUBSTITUTE(A1,"",REPT(" ",20)),20)+0[/TD]
[/TR]
</tbody>[/TABLE]

should be:

[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]=RIGHT(SUBSTITUTE(A1,"\",REPT(" ",20)),20)+0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

should be:

[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]=RIGHT(SUBSTITUTE(A1,"\",REPT(" ",20)),20)+0[/TD]
[/TR]
</tbody>[/TABLE]

Well spotted! It wasn't actually a typo as you could see that the formula did produce the correct result in F1. Rather, it was the forum rendering of the code from the HTML maker, removing the "&bsol;" character.

In any case I have fixed it in my post now and thank you for pointing it out.

 

[addepallibabu]

Thank you So much

 

[addepallibabu]

Thank you so much, is there any single formula that applicable?

 

[Peter_SSs]

is there any single formula that applicable?

Yes there is, but why replace 5 relatively short formulas that do the job quite efficiently with 5 very long ones that are much less efficient, just because they happen to be the same?