Question related to Split cell

Frequent Contributor

Hello Everyone,

I want Employee id, Title, Number, First name and Last name in different columns. How can we split with different columns with the help of FORMULA?

Here is a screenshot.

Screenshot (2274).png

 

For example(with the help of formula) - 

Screenshot (2275).png

 

Please help...???

 

 

Here is a attached file.

 

 

7 Replies

@Zan_Hanifee 

In B2: =SUBSTITUTE(LEFT(SUBSTITUTE(A2,"_",REPT("_",255)),255),"_","")

In C2: =SUBSTITUTE(MID(SUBSTITUTE(A2,"_",REPT("_",255)),256,255),"_","")

In D2: =--SUBSTITUTE(MID(SUBSTITUTE(A2,"_",REPT("_",255)),512,255),"_","")

In E2: =SUBSTITUTE(MID(SUBSTITUTE(A2,"_",REPT("_",255)),768,255),"_","")

In F2: =SUBSTITUTE(MID(SUBSTITUTE(A2,"_",REPT("_",255)),1024,255),"_","")

 

Fill down.

 

It worked!!Thank you so much sir
Sir can you please explain these formula(for B2 and D2)?
In D2, why put -- ??

@Zan_Hanifee 

REPT("_",255) is a string of 255 underscores.

 

SUBSTITUTE(A2,"_",REPT("_",255)) replaces every underscore in the value of A2 with 255 underscores, so it will look like the following, but with lots more underscores

 

Emp1____________Shree___________101____________Karan____________Kulshrestha

 

LEFT(SUBSTITUTE(A2,"_",REPT("_",255)),255) extracts the first 255 characters, it will look like

 

Emp1_______________________

 

Finally, SUBSTITUTE(LEFT(SUBSTITUTE(A2,"_",REPT("_",255)),255),"_","") removes all the underscores.

 

Similarly in D2, MID(SUBSTITUTE(A2,"_",REPT("_",255)),512,255) extracts 255 characters starting at position 512. This will look like

 

___________________101__________________________

 

SUBSTITUTE(MID(SUBSTITUTE(A2,"_",REPT("_",255)),512,255),"_","") removes the underscores, so you end up with 101 as a text value. The first - converts this text value to the number -101, the second - turns that into the number 101.

Now I fully understand sir. Thank you so much

@Zan_Hanifee 

 

You may also use the following formula in B2 and copy it across and down the rows.

 

In B2

=TRIM(MID(SUBSTITUTE($A2,"_",REPT(" ",LEN($A2))),(COLUMNS($B2:B2)*LEN($A2))-LEN($A2)+1,LEN($A2)))

and then copy it across and down.

 

 

it Worked!! Thank you so much sir
You're welcome @Zan_Hanifee!