:help: A coma and Space.

martyavila

New Member
Joined
Jan 21, 2005
Messages
3
:oops: 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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Try this formula:

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

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

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi,

try
Code:
=LEFT(A2,FIND(" ",A2)-1)&", "&MID(A2,FIND(" ",A2)+1,9^9)
hope this helps
jindon
 

mezr

Active Member
Joined
Feb 2, 2003
Messages
301
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

Active Member
Joined
Feb 2, 2003
Messages
301
Good one Tazguy37! I never noticed the "REPLACE" function before. Thanks for posting that one.
 

martyavila

New Member
Joined
Jan 21, 2005
Messages
3
:pray: 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.
 

Forum statistics

Threads
1,148,219
Messages
5,745,452
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top