Text to columns with formula

[addepallibabu]

Hi Everyone,

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

Sachin Kumar Tendulkar *02-02-017*\15000

Mahendra Singh Dhoni \*03-02-2017*\40000

Virat Kohli *04-02-2017*\65000

Ajinkya Rahane Kumar *05-02-2017*\90000

Shikhar Dhawan *06-02-2017*\11500

Rohit Sharma *07-02-2017*\14000 Example: Sachin Kumar Tendulkar 2/2/2017 15000 <tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[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:

F1	=RIGHT(SUBSTITUTE(A1,"",REPT(" ",20)),20)+0

<tbody>
</tbody>

should be:

F1	=RIGHT(SUBSTITUTE(A1,"\",REPT(" ",20)),20)+0

<tbody>
</tbody>

 

[Peter_SSs]

should be:

F1	=RIGHT(SUBSTITUTE(A1,"\",REPT(" ",20)),20)+0

<tbody>
</tbody>

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?