Replace text "Smith, Joe" into "Joe Smith" for a single cell

[flyingmachine]

I have a separate program that generates a report that I open in Excel and for customer name, it returns it as: "Smith, Joe" into a single cell. I want it to read "Joe Smith" instead. I have found a way to separate the first and last names into a separate cell each and then combine back into "Joe Smith", but I was hoping something could be simpler.


Thanks!

[swatsp0p]

Use a helper column next to your list of names (insert a column if needed) and use this formula:

=RIGHT(A1,FIND(",",A1,1)-3)&" "&LEFT(A1,FIND(",",A1)-1) assuming your names begin in A1. Copy this formula down the range of names.

Smith, Joe returns Joe Smith


HTH

Bruce

[BenjieLop]

I have a separate program that generates a report that I open in Excel and for customer name, it returns it as: "Smith, Joe" into a single cell. I want it to read "Joe Smith" instead. I have found a way to separate the first and last names into a separate cell each and then combine back into "Joe Smith", but I was hoping something could be simpler.


Thanks!

I do not know how you define "hoping something could be simpler" but here is something you can use.

Assuming that your customer name (formatted "Last Name, First") is in Cell A1, you can use this formula:

=trim(mid(A1,find(",",A1)+1,255)&" ",left(A1,find(",",A1)-1))

Hope this helps.

Regards.

[flyingmachine]

Use a helper column next to your list of names (insert a column if needed) and use this formula:

=RIGHT(A1,FIND(",",A1,1)-3)&" "&LEFT(A1,FIND(",",A1)-1) assuming your names begin in A1. Copy this formula down the range of names.

Smith, Joe returns Joe Smith


HTH

Bruce

Thanks, but for different combinations of names, some letters get chopped...

smith, joseph eph smith
Johnson, Steve Steve Johnson
Stevenson, Jennifer ennifer Stevenson
Wooden, Tom Tom Wooden
Jordan, Michael hael Jordan

[flyingmachine]

I do not know how you define "hoping something could be simpler" but here is something you can use.

Assuming that your customer name (formatted "Last Name, First") is in Cell A1, you can use this formula:

=trim(mid(A1,find(",",A1)+1,255)&" ",left(A1,find(",",A1)-1))

Hope this helps.

Regards.

I tried to c/p that formula and this error came back:

"You've entered too many arguments for this function.

To get help with entering arguments for the function, click OK to close this message. Then, on the formula bar, click the equal sign button"

[swatsp0p]

My Bad! Here is the corrected formula... must have been too early for my brain... sorry for the confusion:

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


Benji's formula also has a slight error, it is missing one '&' and its replaced by an uneeded comma:

should be:

=trim(mid(A1,find(",",A1)+1,255)&" "&left(A1,find(",",A1)-1))

Either formula will get what you need....your choice.

smith, joseph = joseph smith
Stevenson, Jennifer = Jennifer Stevenson

All better now!

Bruce

[flyingmachine]

Very nice, thanks so much for your help!