Names in different formats

[sushil10s]

Hi,

Sharma , Sushil Kant
Sharma, Sushil Kant
Sharma ,Sushil Kant
Sharma , Sushil Kant
Sushil Kant Sharma

Assuming last word as last name in the last format, I want all the above names in:

Sharma, Sushil Kant

Removing all unnecessary spaces.

Thanks in advance.

[Ron Coderre]

With your sample data in A1:A5

Sharma , Sushil Kant
Sharma, Sushil Kant
Sharma ,Sushil Kant
Sharma , Sushil Kant
Sushil Kant Sharma

This regular formula begins the list of transformations

B1: =TRIM(IFERROR(MID(A1,FIND(",",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(",",A1)-1),A1))

Copy that formula down through B5

These will be the results:

Sushil Kant Sharma
Sushil Kant Sharma
Sushil Kant Sharma
Sushil Kant Sharma
Sushil Kant Sharma

Is that something you can work with?

[sushil10s]

Sorry to bother you, but as I mentioned above, correct format is: Sharma, Sushil Kant

[bebo021999]

Hi Ron, you give "Sushil Kant Sharma" while OP expects "Sharma, Sushil Kant"
I suggest:

=IF(ISNUMBER(SEARCH(",",A1)),TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",255)),255))&", "&TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",255)),255)),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))&", "&SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),""))

Hope this help

[sushil10s]

Absolutely correct...

and thanks for your correct formula.

[sushil10s]

Hi Ron, you give "Sushil Kant Sharma" while OP expects "Sharma, Sushil Kant"
I suggest:

=IF(ISNUMBER(SEARCH(",",A1)),TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",255)),255))&", "&TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",255)),255)),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))&", "&SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),""))

Hope this help

I just want to know why REPT(" ",255) is used.

[bebo021999]

I just want to know why REPT(" ",255) is used.

Small example: "Sushil Shama" change to "Shama Sushil"
You can use SEARCH the position of space betweens 2 words (=7), then use MID(text,7+1,LEN(text)) to get "Shama", then LEFT(text,7-1) to get "Sushil", then concanate both with "&"
Sometime, there are more words, and have more space inside, while SEARCH space only returns first position of first space (i.e Sushil Kan Shama)
I usually use REPT(" ",255) to add 255 spaces btw words:
Sushil ... Shama
And RIGHT(...,255) always return " Shama", then use TRIM to get "Shama"
And while 255? Because the max number of characters in cell is 255, so using 255 is in general.
Hope it is clear for you

[Ron Coderre]

You're right...I mis-read the requirements.

This is the correct formula I should have posted:

B1: =TRIM(IFERROR(TRIM(LEFT(A1,FIND(",",A1)-1))&", "&MID(A1,FIND(",",A1)+1,LEN(A1)),RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))&", "&LEFT(
SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*2)))

And these are the results:

Sharma, Sushil Kant
Sharma, Sushil Kant
Sharma, Sushil Kant
Sharma, Sushil Kant
Sharma, Sushil Kant