Changing Capitalization and Text Order Within a Cell

I've got a program that stores names one way but I want Excel to display them in another.

When I import a name from another program, I get the name in this format 'DOE John'. (No single apostrophes. No commas in between, just a space. The last name, obviously is DOE).

I know the proper () function exists, but is there a function, or combination of functions, that could change 'DOE John' to 'John Doe'? Thanks!

try this
=PROPER(MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)))

or
=PROPER(RIGHT(A1,FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)))

Originally Posted by acerrhod

try this
=PROPER(MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)))

Originally Posted by acerrhod

or
=PROPER(RIGHT(A1,FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)))

Thanks! Any difference between the two?...

Originally Posted by acerrhod

or
=PROPER(RIGHT(A1,FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)))

This one is working fine...

Originally Posted by acerrhod

try this
=PROPER(MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)))

This one is working weird for me... Somehow 'DOE John' became 'Ohn Doe'

Thanks! Really helpful!

the 2nd one is the simplest method if only the text has Last Name and SINGLE NAME

the 1st one works for LAST NAME and DOUBLE NAME (corrected formula)
=PROPER(MID(A1,FIND(" ",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)))

Sample A1
SAMSON Mark Anthony

Originally Posted by acerrhod

or
=PROPER(RIGHT(A1,FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)))

Ok, I just noiced this one changes 'DOE John' to 'John Doe ' (there's a space character after 'Doe'!) Is there any way to get rid of that? Thanks!

yes, -1 to the LEFT(A1,FIND(" ",A1)) after Find(" ",A1)-1

=PROPER(RIGHT(A1,FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1))

Originally Posted by acerrhod

... =PROPER(RIGHT(A1,FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1))

Thanks!

Originally Posted by acerrhod

yes, -1 to the LEFT(A1,FIND(" ",A1)) after Find(" ",A1)-1

=PROPER(RIGHT(A1,FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1))

I tried this one out and the result I got from 'DOE John' is 'Ohn Doe' ('D' is gone and there are 2 spaces between last name and first name. Thanks!

Originally Posted by olives

I tried this one out and the result I got from 'DOE John' is 'Ohn Doe' ('D' is gone and there are 2 spaces between last name and first name. Thanks!

acerrhod, could you take another look at this formula?... By the way, can it be adjusted to get rid of any spaces before (!) the last name (EXAMPLE: ' DOE') and any AFTER the last name (EXAMPLE: 'DOE ')... Thanks!

Originally Posted by martindwilson

=proper(right(trim(a1),find(" ",trim(a1)))&" "&left(trim(a1),find(" ",trim(a1))-1))

Thanks! I'll try it tonight!

Originally Posted by olives

Thanks! I'll try it tonight!

Not bad! Thanks! I've got a few mistakes, but I'll try and post them later with examples Thank you!

Originally Posted by martindwilson

=proper(right(trim(a1),find(" ",trim(a1)))&" "&left(trim(a1),find(" ",trim(a1))-1))

I'm getting a few mistakes when people have two last names or they include their middle names.... Can somebody review this so that the first word - which is always capitalized - is always the last in the result? I'll deal with double last names and middle names on an exception basis, thanks!

Originally Posted by olives

... I've got a few mistakes...

Here's an example:

For 'DOE John' I get 'John Doe'

But for 'STANDARD Joe' I get 'Ndard Joe Standard'...

With the formula:

=PROPER(RIGHT(TRIM(A1),FIND(" ",TRIM(A1)))&" "&LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1))

can you site sample

Hi,

Is the middle name was initial only i.e. DOE John T.?
can you site an example

Originally Posted by martindwilson

... i never actually tested the previous one...

I think it's working! I'll try a few more difficult names later tonight, just in case! Thanks!

Originally Posted by acerrhod

... Is the middle name... initial only i.e. DOE John T.? ...

Hi, acerrhod... To answer your question:

When a middle name appears it's the full middle name, for example:

DOE John Henry

What I'll check tonight is how the formula works with double last names, which are used a lot in Latin America and Spain, for example:

RODRIGUEZ RIVERA Jose Luis

In that case (two last names) I'd be ok with losing the 2nd LAST NAME (in the example above I would be ok with the result being, for example, 'Jose Luis Rodriguez'.

Thanks for the inquiry and the chance to clarify!

Originally Posted by olives

Hi, acerrhod... To answer your question:

When a middle name appears it's the full middle name, for example:

DOE John Henry

What I'll check tonight is how the formula works with double last names, which are used a lot in Latin America and Spain, for example:

RODRIGUEZ RIVERA Jose Luis

In that case (two last names) I'd be ok with losing the 2nd LAST NAME (in the example above I would be ok with the result being, for example, 'Jose Luis Rodriguez'.

Thanks for the inquiry and the chance to clarify!

hi,
try this (is too long formula)
=TRIM(RIGHT(A1,LEN(A1)-(SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),"")))-1)))&" "&PROPER(LEFT(A1,SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("65:90"))),"")))-1))

the only problem to this is has single space in the result text if the Last name is single only i.e. DOE John Henry