Separate Name's Id

[naveenakh]

How do I split the name's & Id with extra spaces

pls find link

http://speedy.sh/k324D/Emp-Name-Id..xlsx


Thanks

 

[Hui]

Naveena


Why not just:


Select Column A2:A37

Data, Text to Columns

Delimited

Select Space and Apply

 

[naveenakh]

Thanks Hui

I did, but not working properly,there so many space in the cell,

I tried clean, trim First Name Middle Name & Last name formula.

So pls give any other solution for joining these names & id properly.

 

[Faseeh]

Hi Naveena,


For code you can use

=RIGHT(A2,7)


For Name you can use =LEFT(A2,LEN(A2)-LEN(C2))

But this name still contains some extra spaces.


Regards,

Faseeh

 

[naveenakh]

is there any solution for removing extra spaces

 

[Faseeh]

Try this for name:

=MID((TRIM(SUBSTITUTE(A2,CHAR(160),""))),1,LEN(TRIM(SUBSTITUTE(A2,CHAR(160),"")))-LEN(C2))

 

[shrivallabha]

If employee code is always 7 letters long then:


=TRIM(SUBSTITUTE(LEFT(A2,LEN(A2)-7),CHAR(160)," "))


will also work.