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


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 -- ??


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




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




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




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



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!