Need to add a comma in between Last and First name, but only in some fields

[James Keuning]

I have an inconsistent field that I need to clean up so I can parse it. The data looks like this:

Smith, Joan
Jones Penny L
Hawkins, Wilma A
Alexander Byron

Is there a function that can examine each field, check for a comma before the first space and 1: If the comma is there just return the existing value and 2: if the comma is not there add the comma and return the value with the comma inserted?

[Jonmo1]

Try

=SUBSTITUTE(SUBSTITUTE(A1,",","")," ",", ",1)

[Olly]

I'd use a small UDF:

Please Login or Register  to view this content.

Use as:

Please Login or Register  to view this content.

[Bailey_Thomas]

So it seems like you just need all names to have the LastName comma FirstName. If you're trying to do that I'd just go with a work around using Find/Replace.
First I'd click on your column of names>open up Find/Replace (Ctrl+F)>type in a space ( ) in "find what">type in a comma then a space (, ) in "replace">this will fix all of the names that don't have commas and put them in there... but now you'll have names with two commas in them... easy fix... click your name column again>Find/Replace (Ctrl+F)>in "find what" type in two commas (,,)>in "replace" type in just one comma (,). This will replace all the double commas and your final outcome will be ALL names on your list will be "LastName, FirstName ".
It's an unorthodox solution but that's just the way my mind thinks. Hope this helps!

Kind Regards,

Tommy Bailey

[Glenn Kennedy]

Yet another way!!

=IF(MID(A1,FIND(" ",A1)-1,1)=",",A1,SUBSTITUTE(A1," ",", ",1))

[James Keuning]

Try

=SUBSTITUTE(SUBSTITUTE(A1,",","")," ",", ",1)

LOVE IT!
(I tried to add a reputation but apparently I've been propping you too much lately!)

[James Keuning]

Apparently I need to study up on my SUBSTITUTE.

[Jonmo1]

Glad it worked out for you