Excel Formula: Add comma between names
Supposing there is a list of customer names in a worksheet, you need to add comma between the first name and last name as below screenshot shown, how can you quickly solve it instead of manual typing commas? Here provides formulas for solving this job in Excel.
Only add comma after first name
If you just want to add comma after first name, you can use a formula which combines FIND and REPLACE functions.
Generic formula:
| REPLACE(txt,FIND(" ",txt),0,",") |
Arguments
| Txt: the name you want to add comma. |
How this formula work
Here is a full name in cell B3, now in cell C3, you can use below formula to add the comma after the first name.
| =REPLACE(B3,FIND(" ",B3),0,",") |
Press Enter key.
Explanation
FINDfunctiin: the FIND function gets the location of a specific character in a text string. Here it is used to find the location of first space in the cell B3, it returns 5.
REPLACE function: the REPLACE function replaces the old text specified by a location in the given text string with a new one. But if the num_char argument in the REPLACE function is zero, it adds the new character. Now here it adds comma before the character in 5th position of the text in cell B3.
Add commas between names (first name, middle name and last name)
If you want to add commas between names as below screenshot shown, you can use the formula which combines SUBSTITUTE function and TRIM function.
Generic formula:
| SUBSTITUTE(TRIM(txt)," ",", ") |
Arguments
| Txt: the full name you used to add comma. |
How this formula work
Example: to add commas between names in range B3:B8, please use below formula:
| =SUBSTITUTE(TRIM(B3)," ",", ") |
Press Enter key, then drag fill handle down to cell B8, now the commas have been added between names.
Explanation
TRIM function: this function used to remove the extra spaces in the text string.
SUBSTITUTE function: this function replaces old text with a new one. Here it replaces all spaces with spaces plus commas.
Note
If you do not need to remove extra space, just directly use the SUBSTITUTE function.
| =SUBSTITUTE((B3)," ",", ") |
Sample File
Click to download sample fileRelative Formulas
- Remove First N Characters From Cell
Here introduces the formula to remove characters from left side of a text string in Excel. - Trim Text To N Words
Here introduces the formula to extract n words from left side of a text string. - Extract The Last Word From Text String In Excel
This tutorial provides a formula to extract last word from the give text string. - Extract The First Word From Text String In Excel
This tutorial provides a formula to extract first word from the give text string.
Relative Functions
- FIND function
Find the position of a character. - REPLACE function
Find and replace characters based on given location from text string with a new text. - TRIM function
Remove extra spaces from text. - SUBSTITUTE
Replace old text with a new one.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
