Help Converting Name Format

[romualdezd]

I have a column of data that conatin author names in the following format- first initial(s), last name
Example: A. Berchuck, R. D. Alvarez, K. Trinkaus, J. S. Rader, D. G. Mutch

I need help converting this data to: last name, first initial(s)
i.e. Berchuck, A.,Alvarez, R.D., Trinkaus, K., Rader, J.S., Mutch, D.G.

Thanks
Dwayne

 

[xlbo]

Have a look at the FIND function (to find the 1st space in the text) and using that alongside the RIGHT function and use & to concatenate

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bluedragon2]

=IF(ISERROR(SEARCH(" ",A1,4)),RIGHT(A1,LEN(A1)-3)&", "&LEFT(A1,2),RIGHT(A1,LEN(A1)-6)&", "&LEFT(A1,5))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlhelp]

Hi, Geoff's idea will work if there is only one initial

=RIGHT(A1, LEN(A1)-IF(ISERROR(FIND(" ",A1,4)+3),3,FIND(" ",A1,4)+3))&", "&LEFT(A1,IF(ISERROR(FIND(" ",A1,4)+3),2,FIND(" ",A1,4)+3))

Member- AAAA Association Against Acronym Abusers

 

[Rofeu]

Hi,

this one will work no matter how many initials there are:

=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND("#",SUBSTITUTE(TRIM(A1)," ","#",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))),1))&", "&LEFT(TRIM(A1),FIND("#",SUBSTITUTE(TRIM(A1)," ","#",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))),1))

Only problem is when the last name consists ofmore than one word.

Cheers,

Roel

 

[MallardVHS]

hi rom,

this issue has been addressed before (and, yes, it's a tricky one).

In addition to the great advice above, I'd recommend searching the TT forums and FAQs for info on the topic.

Best wishes!
don

[blue]________________________________________________________
Some folks put their money where their mouth is. I just pay my bill. [/blue]

 

[xlhelp]

I already found a small error in logic in my formula. It should be:

=RIGHT(A1, LEN(A1)-IF(ISERROR(FIND(" ",A1,4)+3),3,FIND(" ",A1,4)))&", "&LEFT(A1,IF(ISERROR(FIND(" ",A1,4)+3),2,FIND(" ",A1,4)))

or a better one to use is

=MID(SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)&", "&LEFT(A1,LEN(A1)-LEN(MID(SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)))

I would go crazy trying to select out of these.

Member- AAAA Association Against Acronym Abusers