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.
|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.
Press Enter key.
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.
|SUBSTITUTE(TRIM(txt)," ",", ")|
|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.
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.
If you do not need to remove extra space, just directly use the SUBSTITUTE function.
|=SUBSTITUTE((B3)," ",", ")|
- 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.
- 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.
Replace old text with a new one.