CODEWITHSUNDEEP
stringexcelformatting
pencilCake
pencilCake

Reputation: 53263

How can I switch a string from "lastName, firstName" to "firstName LastName"?

I have a column full of names written as:

"lastName, firstName"

I want to have a nother column that has this name list written as:

"firstName LastName"

So, how can I switch a string from "lastName, firstName" to "firstName LastName" ?

Upvotes: 7

Views: 63448

Answers (5)

Cenay
Cenay

Reputation: 1

Barry's answer is correct, if the single cell (A2) doesn't ALSO contain errors like "firstname lastname" -- as is often the case.

So, to deal with the cell data being either "Last, First" or "First Last", we'll need to compare the cell and take action appropriately. Also note, in my example, I moved the first name to it's own column, and last name to it's own column. You can of course join them into a single column.

First, let's break down what we need. This will return the first name when there is a "," in the cell contents: (assumes the field you are wanting to split is in A2, adjust as needed)

=MID(A2;FIND(",";A2)+2;99)

and this will return the first name when there isn't:

=MID(A2;1;FIND(" ";A2))

And for Last name when there is a comma:

=MID(A2;1;FIND(",";A2)-1)

and last name when there isn't:

=MID(A2;FIND(" ";A2)+1;99)

So, we need to test if there is a "," in the string, and deal with an error condition when there isn't (FIND returns #value when not found, ISERROR() can test for that). Optionally (and my approach) was to check if the return of the FIND yielded a number (an error won't). In this case, an "error condition of not finding it" would yield a non-number #Value.

=IF( ISNUMBER( FIND(",";A2)); MID(A2;FIND(",";A2)+2;99); MID(A2;1;FIND(" ";A2)))

Doing the same thing for the Last name looks like this:

=IF( ISNUMBER( FIND(","; A2)); MID(A2;1;FIND(",";A2)-1); MID(A2;FIND(" ";A2)+1;99))

Please note that my solution was using my spreadsheet application OpenOffice. I think Excel has a different find method (Search?) - so make adjustments as necessary.

Upvotes: 0

Excel Dude
Excel Dude

Reputation: 1

The worked for me =RIGHT(C3,LEN(C3)-LEN(LEFT(C3,FIND(",",C3)-1))-1) & " " & LEFT(C3,FIND(",",C3)-1)

Upvotes: 0

Jay Wind
Jay Wind

Reputation: 11

The above answer is incorrect. In fact, for my own name, Jay Jacob Wind, the formula breaks. The correct formula is:

Assuming Last, First is in column A, separated by one comma and a space bar

For first name:

=mid(A2,find(",",A2)+2,99)

For last name:

=mid(A2,1,find(",",A2)-1)

Put together, assuming Column B is First Name and Column C is Last Name =B2&" "&C2

or simply (but more complexly)

=mid(A2,find(",",A2)+2,99)&" "&mid(A2,1,find(",",A2)-1)

but I like separating them into two columns, then concatenating them

Upvotes: -2

Conroy Daley
Conroy Daley

Reputation: 11

Enter data into cells e.g. 

Brown, John
Green, Bob
Smith, Will

(Note that the comma in this case is the delimiter which the system will use to separate the entries) Highlight these cells.
Click on the "Data" tab, click on "Text to Column".
Choose options offered.

Upvotes: 1

barry houdini
barry houdini

Reputation: 46371

If the first name is in A2 try this formula in B2 copied down

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

Upvotes: 18

Related Questions