Question related to Split cell

[Excel]

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.

For example(with the help of formula) - 

Please help...???

Here is a attached file.

[#2]

@Excel 

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.

[#3]

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

[#4]

@Excel 

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.

[#5]

Now I fully understand sir. Thank you so much:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:

[#6]

@Excel 

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.

[#7]

it Worked!! Thank you so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:

[#8]

You're welcome @Excel!