Rearranging Name Format

[Rocko]

Using a formula I wish to be able to convert a whole column of names
formatted as follows

1. SMITH, Dane
2. ALLENBY, Gary
3. MCDONALD, Leigh
4. O'CONNOR, Paul
5. MAYFIELD-SMITH, Jimmy
6. HARRISON, Lenny

to

Dane Smith
Gary Allenby
Leigh McDonald
Paul O'Connor
Jimmy Mayfield-Smith
Lenny Harrison

I have hundreds of names that need to be converted so changing manually
would be very time consuming. Any help on this would be greatly appreciated.

 

[Pete_UK]

With your names in column A starting with A1, put this in B1:

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

and copy down to the bottom of your list of names. You will get this:

Dane Smith
Gary Allenby
Leigh Mcdonald
Paul O'Connor
Jimmy Mayfield-Smith
Lenny Harrison

with your test data, which is not quite what you wanted (note
McDonald).

Hope this helps.

Pete

 

[Gary''s Student]

Try:

=MID(A1,FIND(", ",A1)+2,256)&" "&PROPER(LEFT(A1,LEN(A1)-FIND(", ",A1)))

 

[Teethless mama]

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

 

[Rocko]

Thanks for your help. This almost works but I may not have made it clear with
my question. I am trying to remove the preceding number from my result. If I
remove the number preceding the original name your formula works great, but
at the moment it is still picking up that number & I am getting the result
"Smith, Dane 1".

 

[Ron Rosenfeld]

Thanks for your help. This almost works but I may not have made it clear with
my question. I am trying to remove the preceding number from my result. If I
remove the number preceding the original name your formula works great, but
at the moment it is still picking up that number & I am getting the result
"Smith, Dane 1".

I think most of us probably thought the numbers were just to show the rows.

But to remove the numbers, replace A1 with a formula that returns the string
that starts after the first ".", and also trims off any spaces, in case some of
your names don't have a space following the number.

e.g., adapting Teethless Mama's formula:

=PROPER(MID(TRIM(MID(A1,FIND(".",A1)+1,99))&" "&TRIM(
MID(A1,FIND(".",A1)+1,99)),FIND(" ",TRIM(MID(A1,FIND(
".",A1)+1,99)))+1,LEN(TRIM(MID(A1,FIND(".",A1)+1,99)))-1))

--ron

 

[Rocko]

That worked perfectly thanks Ron. You guys are a fantastic help. Have a good
day.

 

[Ron Rosenfeld]

That worked perfectly thanks Ron. You guys are a fantastic help. Have a good
day.

Glad to help, as I'm sure are the others. Thanks for the feedback.
--ron