Changing cell contents

  • Thread starter Thread starter Gerd
  • Start date Start date
G

Gerd

How do I change

Smith J.
Jones L.
Cyr M.

into

J. Smith
L. Jones
M. Cyr

The original list is A1:A3 and I would like the second
list in B1:B3

Thank you for any help
 
Gerd,

The following will only work based on the example that you gave.
This will not work for Smith, J.L., Mr. Smith J.L.
I'm assuming that there are no comma's between last name first name.

having said that......
critical issue is where is the space character???
Using Smith J. as an example.....in Cell A1
=FIND(" ",A1)
returns a 6
Next isssue is how long is the entire string......
=LEN(A1)
returns an 8
So at this point, we know that there's an 8 character string where the
6th character is a space.
A step further......
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
If the string is 8 characters long, and the space is at the 6th place in
the
string then the right function of the length minus where the space is
will
return "J."
Next step.......
=LEFT(A1,FIND(" ",A1)-1)
Get the left (where the space is minus 1) characters of A1 and
that should result in "Smith"
Last step.......
Concatenate the two placing a space in between....
=RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)

For more on this, take a look here:
http://www.cpearson.com/excel/FirstLast.htm

John
 
Back
Top