Convert Name from last to first first to last. [SOLVED]

[Shayeebur]

Hi

I want to change name from Smith, John to John Smith & John Smith to Smith, John, how can I do via formula & not using text column feature pls advice I have attached the excel file.

http://www12.zippyshare.com/v/36331786/file.html

 

[Debraj]

Hi Shayeebur


There was a huge list of post for the same.. try to Google in the top right of the screen..


or http://bit.ly/15De8Po

 

[SirJB7]

Hi, Shayeebur!


Try this:

I4: =DERECHA(H4;LARGO(H4)-HALLAR(", ";H4))&" "&IZQUIERDA(H4;HALLAR(", ";H4)-1) -----> in english: =RIGHT(H4,LEN(H4)-SEARCH(", ",H4))&" "&LEFT(H4,SEARCH(", ",H4)-1)

I5: =DERECHA(H5;LARGO(H5)-HALLAR(" ";H5))&", "&IZQUIERDA(H5;HALLAR(" ";H5)-1) -----> in english: =RIGHT(H5,LEN(H5)-SEARCH(" ",H5))&", "&LEFT(H5,SEARCH(" ",H5)-1)


The general formula is:

=RIGHT(<source>,LEN(<source&#62-SEARCH(<source separator>,<source&#62)&<target separator>&LEFT(<source>,SEARCH(<source separator>,<source&#62-1)

In both cases the formula is the same, just change source and target separators, ", "/" " & " "/", " respectively.


Just advise if any issue.


Regards!

 

[SirJB7]

@Debraj Roy

Hi!

It seems as if I left this window open too much time without updating it...

Regards!

PS: BTW, am I not so kind and so willingly?

 

[Colin Legg]

This is one of my favourite formulas, in I4:

=MID(H4&" "&H4,SEARCH(" ",H4)+1,LEN(H4)-1)


In I5

=MID(H5&", "&H5,SEARCH(" ",H5)+1,LEN(H5)+2)

 

[SirJB7]

@Colin Legg

Hi!

Very nice and smart approach

Regards!

 

[Colin Legg]

Thanks, SirJB. Shame I didn't come up with it... I picked it up from the MrExcel board (I think) many moons ago.

 

[SirJB7]

@Colin Legg

Hi!

Should I write the same next time I faced such an issue? As I intend to use "your" method.

Regards!

 

[Shayeebur]

Hi SirJB7 & Colin

Thanks for answers, thanks a lot.

 

[oldchippy]

@Colin


In I5

=MID(H5&", "&H5,SEARCH(" ",H5)+1,LEN(H5)+2)


I think this should be


In I5

=MID(H5&","&H5,SEARCH(" ",H5)+1,LEN(H5)+2)

 

[oldchippy]

@Colin,


Ignore me :-(

 

[SirJB7]

Hi, Shayeebur!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!