Format Name
 

Can someone please help with a formula?

In Column A I have names formatted as SMITH, John. In Column B I need John
Smith. Would someone please help with a formula that would switch the names,
get rid of the comma, and change the case to lower case of the last name
except for the first letter?

Thanks in advance!
- D.

Format Name
 

maybe this in b1 and dragged down

=MID(A1,FIND(",",A1,1)+2,LEN(A1))&" "&LOWER(LEFT(A1,FIND(",",A1,1)-1))

Mike

"Dax Arroway" wrote:

Can someone please help with a formula?

In Column A I have names formatted as SMITH, John. In Column B I need John
Smith. Would someone please help with a formula that would switch the names,
get rid of the comma, and change the case to lower case of the last name
except for the first letter?

Thanks in advance!
- D.

Format Name
 

One way:

=PROPER(TRIM(MID(A1,FIND(",",A1)+1,255) & " " &
LEFT(A1,FIND(",",A1)-1)))

In article ,
Dax Arroway wrote:

Can someone please help with a formula?

In Column A I have names formatted as SMITH, John. In Column B I need John
Smith. Would someone please help with a formula that would switch the names,
get rid of the comma, and change the case to lower case of the last name
except for the first letter?

Thanks in advance!
- D.

Format Name
 

Thanks Mike but the first letter of the last name also goes lower case. How
do I get the first letter to remain capitalized?

"Mike H" wrote:

maybe this in b1 and dragged down

=MID(A1,FIND(",",A1,1)+2,LEN(A1))&" "&LOWER(LEFT(A1,FIND(",",A1,1)-1))

Mike

"Dax Arroway" wrote:

Can someone please help with a formula?

In Column A I have names formatted as SMITH, John. In Column B I need John
Smith. Would someone please help with a formula that would switch the names,
get rid of the comma, and change the case to lower case of the last name
except for the first letter?

Thanks in advance!
- D.

Format Name
 

It's all to do with spaces, see your other reponse where spaces are trimmed
out.

Mike

"Dax Arroway" wrote:

Thanks Mike but the first letter of the last name also goes lower case. How
do I get the first letter to remain capitalized?

"Mike H" wrote:

maybe this in b1 and dragged down

=MID(A1,FIND(",",A1,1)+2,LEN(A1))&" "&LOWER(LEFT(A1,FIND(",",A1,1)-1))

Mike

"Dax Arroway" wrote:

Can someone please help with a formula?

In Column A I have names formatted as SMITH, John. In Column B I need John
Smith. Would someone please help with a formula that would switch the names,
get rid of the comma, and change the case to lower case of the last name
except for the first letter?

Thanks in advance!
- D.

Format Name
 

PERFECT! Thanks so much! You guys rock. I'd have never figured that out on
my own.

"JE McGimpsey" wrote:

One way:

=PROPER(TRIM(MID(A1,FIND(",",A1)+1,255) & " " &
LEFT(A1,FIND(",",A1)-1)))

In article ,
Dax Arroway wrote:

Can someone please help with a formula?

In Column A I have names formatted as SMITH, John. In Column B I need John
Smith. Would someone please help with a formula that would switch the names,
get rid of the comma, and change the case to lower case of the last name
except for the first letter?

Thanks in advance!
- D.

Format Name
 

If you come back to this thread, here is a shorter alternative method...

=PROPER(MID(A7&" "&A7,FIND(" ",A7)+1,LEN(A7)-1))

Rick


"Dax Arroway" wrote in message
...
Can someone please help with a formula?

In Column A I have names formatted as SMITH, John. In Column B I need
John
Smith. Would someone please help with a formula that would switch the
names,
get rid of the comma, and change the case to lower case of the last name
except for the first letter?

Thanks in advance!
- D.