Convert LNAME,FNAME to FNAME LNAME and allow BIZ names

[lmohr79]

I have a column of personal names that I need to convert, but mixed in are some business names.

Here's the formula I've come up with - A1 can either be "Smith,John A" or "ABC Business". For all personal names, there is 1 comma, no commas in business names:

=IF(FIND(",",A1)>0,MID(A1,(FIND(",",A1)+1),LEN(A1)-FIND(",",A1))&" "&LEFT(A1,(FIND(",",A1)-1)),A1)

This works great if the comma is in the cell, but for a business name, no comma, so the result is #VALUE!. As you can see, I'd just like the contents of the cell if there's no comma

Can't seem to find a "CONTAINS" function, which would be ideal.

Thank you!

 

[AnotherHiggins]

Try this by itself:
[tab]=SEARCH(",", A4)
The problem is, it doesn't return a zero if no comma is found, it returns a #VALUE! error.

Instead, use this:
[tab]=IsNumber(Search(",", A4))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lmohr79]

Thanks for the info - works like a charm!