How do I change "Doe, John" to "John Doe" in Excel?

[Guest]

Imported text from another program shows "Doe, John". Does anyone know how
to break it into "John" and "Doe"?

 

[Niek Otten]

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

 

[Earl Kiosterud]

Moon,

There are a few ways. A couple are here. Not too robust, I fear,

Data - Text to Columns. Make sure there are empty columns to the right to
contain the parsed stuff. Select your column. Data - Text to Columns,
Delimited, Delimiter: comma. This will split Doe, John into two columns.
It won't preserve the original data (the last name will wind up in the in
the original column), and will use an additional column for the first name.
If there are additional commas, it will use more columns. The space after
the comma will be included in the first name. It sometimes doesn't put the
data in the same rows as was the original.

You can easily rearrange the columns laterally if you need the first name
column at the left by selecting a column, then edge-dragging holding the
Shift key.

Another way is with two helper columns:

Last Name: =LEFT(A2,SEARCH(",",A2)-1)
First Name: =MID(A2,SEARCH(",",A2)+2,LEN(A2))

The first name formula depends on the space after the comma, and will lop
off the first character of the first name if it isn't there. If that's a
possibility, use:

=MID(A2,SEARCH(", ",A2)+2,LEN(A2))

Note the space after the comma in the search string. This will fail with
#VALUE! in such a situtation, which is likely better than a messed-up first
name.

Earl Kiosterud
www.smokeylake.com

 

[Ron Rosenfeld]

Imported text from another program shows "Doe, John". Does anyone know how
to break it into "John" and "Doe"?

If the last name always ends with a ",", and the first name is always the last
word in the text string, then, with the string in A1:

FN:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)

LN: =LEFT(A1,FIND(",",A1)-1)

The formulas will give errors if there is no comma, or space in the string.

If you require more complicated pattern matching, then post back, as regular
expressions could be used to advantage here.


--ron