Hi-
I think the problem with Dave's solution is that it puts a comma afte
every first name regrdless of whether there is a middle initial o
not.
I have a solution. It's involved, but if you have a large number o
names it may be worth your while.
First, assuming the names are in column A, insert 5 new columns to th
right. These can be deleted later. In column B, enter the numbers 1 t
x (x = the total number of names). This will allow you to sort the
back in their original order at the end.
Then select column A and use DATA, TEXT TO COLUMNS .
Select DELIMITES BY SPACE, and select cell $C$1. This will give yo
the first name, middle initial, and last name in columns C,D,&E.
Highlght these three columns and select copy and paste special
values.
Next, select columns B thru E and do a data sort using column E to sor
by. This will separate those names with a middle initial from thos
without one.
In cell F1, enter the formula =C1&", "&D1&" "&E1. Copy this down al
the way to the end of the names with the middle initial.
In the cell in Column F next to the name with no first initial (assum
F355) enter the fromula =C355&" "&D355&" "&E355. Copy this down to th
end of your names.
Now, select Column F and select copy. Select cell A1 and PAST
SPECIAL, VALUES. Select columns A & B and sort using column B.
You will now have the names in column A formatted as you want them i
back into their original order. You can then select columns B thru
and delete them.
I suggest you try this on a sample sheet with only two or thre names o
each type, so that you can see how it works.
There may be an easier solution, but I'n not sufficiently experience
to tell you what it is.
Good luck
