:help: A coma and Space.

[martyavila]

1st and most, thank you for your help.
I have a workbook that has names like these:

Smith Thomas C
Frances Adelle
Avila Sabrina O
Gonzalez Rodolfo
Gill Richard W
Cannif Catherine
etc...1000+ more names.

What I need to do to these names is: add a "," (coma) at the end
of the 1st word (last name) keeping the space as well; I have
tried a formula but this put a "," at every space.
I have tried multiple ways, but to no avail.

any sugestions will be greatfuly appreciated.

 

[Tazguy37]

Try this formula:

=REPLACE(A1,FIND(" ",A1),1,", ")

where A1 contains your text to search. Hope that helps!

 

[jindon]

Hi,

try

=LEFT(A2,FIND(" ",A2)-1)&", "&MID(A2,FIND(" ",A2)+1,9^9)

hope this helps
jindon

 

[mezr]

One thought is to first separate each part of the name into columns. This is quickly done by highlighting the column that contains the names and then selecting "Data" from the menu bar. Choose the "Text to Columns" option.

Once the names are broken up into three separate columns (columns A, B, C) then in the forth column (column D) enter the formula: =A1&", "&B1&" "&C1. Then drag the formula down the sheet.

 

[mezr]

Good one Tazguy37! I never noticed the "REPLACE" function before. Thanks for posting that one.

 

[martyavila]

ray: Even if I type a 1000 words it will be not enough to thank you all
specially to TAZGUY37.

May God always bless your givings.