Question related to Split cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2234666%22%20slang%3D%22en-US%22%3EQuestion%20related%20to%20Split%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234666%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%3CSPAN%3EI%26nbsp%3Bwant%20%3CSTRONG%3EEmployee%20id%3C%2FSTRONG%3E%2C%20%3CSTRONG%3ETitle%3C%2FSTRONG%3E%2C%20%3CSTRONG%3ENumber%3C%2FSTRONG%3E%2C%20%3CSTRONG%3EFirst%20name%3C%2FSTRONG%3E%20and%20%3CSTRONG%3ELast%20name%3C%2FSTRONG%3E%20in%20different%20columns.%26nbsp%3B%3C%2FSPAN%3EHow%20can%20we%20split%20with%20different%20columns%20with%20the%20help%20of%20FORMULA%3F%3C%2FP%3E%3CP%3EHere%20is%20a%20screenshot.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(2274).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266938iF01835AFEBF3C4AA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(2274).png%22%20alt%3D%22Screenshot%20(2274).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example(with%20the%20help%20of%20formula)%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(2275).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266940i6E37CEA9E6D38A8A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(2275).png%22%20alt%3D%22Screenshot%20(2275).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20attach%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2234666%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2234757%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Split%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234757%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B2%3A%20%3DSUBSTITUTE(LEFT(SUBSTITUTE(A2%2C%22_%22%2CREPT(%22_%22%2C255))%2C255)%2C%22_%22%2C%22%22)%3C%2FP%3E%0A%3CP%3EIn%20C2%3A%20%3DSUBSTITUTE(MID(SUBSTITUTE(A2%2C%22_%22%2CREPT(%22_%22%2C255))%2C256%2C255)%2C%22_%22%2C%22%22)%3C%2FP%3E%0A%3CP%3EIn%20D2%3A%20%3D--SUBSTITUTE(MID(SUBSTITUTE(A2%2C%22_%22%2CREPT(%22_%22%2C255))%2C512%2C255)%2C%22_%22%2C%22%22)%3C%2FP%3E%0A%3CP%3EIn%20E2%3A%20%3DSUBSTITUTE(MID(SUBSTITUTE(A2%2C%22_%22%2CREPT(%22_%22%2C255))%2C768%2C255)%2C%22_%22%2C%22%22)%3C%2FP%3E%0A%3CP%3EIn%20F2%3A%20%3DSUBSTITUTE(MID(SUBSTITUTE(A2%2C%22_%22%2CREPT(%22_%22%2C255))%2C1024%2C255)%2C%22_%22%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular 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!