Mar 25 2021 02:24 AM - edited Mar 25 2021 02:55 AM
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.
Mar 25 2021 03:20 AM
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.
Mar 25 2021 03:26 AM
Mar 25 2021 03:39 AM
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.
Mar 25 2021 03:42 AM
Mar 25 2021 03:58 AM
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.
Mar 25 2021 04:04 AM