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.

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

On Feb 4, 11:44*pm, Rocko wrote:
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.

Try:

=MID(A1,FIND(", ",A1)+2,256)&" "&PROPER(LEFT(A1,LEN(A1)-FIND(", ",A1)))
--
Gary''s Student - gsnu201001


"Rocko" wrote:

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.

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


"Rocko" wrote:

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.

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".

"Teethless mama" wrote:

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


"Rocko" wrote:

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.

On Thu, 4 Feb 2010 20:02:04 -0800, Rocko
wrote:

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".

"Teethless mama" wrote:

=PROPER(MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1)-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,9 9)))-1))

--ron

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

"Ron Rosenfeld" wrote:

On Thu, 4 Feb 2010 20:02:04 -0800, Rocko
wrote:

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".

"Teethless mama" wrote:

=PROPER(MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1)-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,9 9)))-1))

--ron
.

On Fri, 5 Feb 2010 18:20:01 -0800, Rocko
wrote:

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