How to quickly add comma between words in Excel?
When working with data in Excel, there are times when you need to separate words or phrases with commas. For example, you might have a list of names, products, or keywords, and you need to format them with commas for further processing. Manually adding commas can be tedious, especially for large datasets. Fortunately, Excel provides efficient ways to automate this task. Below is a detailed guide to quickly adding commas between words in Excel.
 |
 |
 |
Add comma between words with Find and Replace function
Add comma between words with Kutools for Excel
Add comma between words with SUBSTITUTE function
Add comma between words with VBA code
Add comma between words with Find and Replace function
Excel's Find and Replace tool enables you to quickly update text within cells. If your data contains words separated by spaces, you can use this function to efficiently place commas between the words.
1. Begin by selecting the cells where you want to insert commas between the words. With the cells selected, press Ctrl + H on your keyboard to open the Find and Replace dialog box.
2. In the dialog box, click the "Find what" field and enter a space using the spacebar (make sure it’s a single space). Next, in the "Replace with" field, type a space followed by a comma (i.e., " ,"). This setup ensures that each space between words will be replaced by a space and a comma, keeping the words readable.
3. Click Replace All. Excel will immediately update all selected cells, inserting a comma after every space throughout your selection.
4. After the replacement, a message will appear showing how many replacements were made. Click OK, then click Close to exit the Find and Replace dialog.
Tips and notes:
- This approach works best when the words are consistently separated by single spaces.
- If your data contains double spaces, tabs, or inconsistent word spacing, consider cleaning up the data with the TRIM function or the Remove Duplicates Spaces feature beforehand.
- Find and Replace alters the original cells directly. If you need to keep your raw data unchanged, make a copy before applying this method.
Add comma between words with Kutools for Excel
If you prefer a more flexible or advanced approach, Kutools for Excel provides dedicated features to automate this process. This add-in extends Excel’s capabilities and offers more customization when inserting text between or around words in cells, which is especially helpful when working with complex data formats or specific delimiter requirements. The Add Text tool can automatically add any character (comma, semicolon, hyphen, etc.) at a variety of positions, minimizing manual intervention while handling large or varied datasets.
Once you’ve installed Kutools for Excel, follow these steps to add commas between the words in your selected cells:
Click on the Kutools tab in Excel’s ribbon, then choose Text > Add Text to open the Add Text dialog box.
Adjust the settings as follows:
- In the "Text" box, enter the comma (or another separator of your choice) that you want to add between the words.
- Next, select the Between every word option from the "Only add to" dropdown. This configuration ensures the selected separator is inserted between each word wherever a space occurs in the cell.
- Finally, click the OK button to apply formatting to your selection.
Result: The selected cells now display each word separated by commas as shown.
Possible issues and tips:
- If you need to use other separators (such as a semicolon, dash, etc.), simply input your desired character in the "Text" field.
- Kutools supports batch operations, making this a convenient solution when handling hundreds or thousands of rows.
Add comma between words with SUBSTITUTE function
For users who prefer using formulas, Excel’s SUBSTITUTE function allows you to insert commas between words without permanently altering the source data. This approach is suitable when you need to generate a separate, formatted version of your data for reports or exports. It’s also safe for dynamic data scenarios, as the formula output automatically refreshes when the original data changes.
Suppose your original text is in cell A1:
Enter the following formula in another cell (e.g., B1):
=SUBSTITUTE(TRIM(A1)," ",", ")Press Enter to confirm. Then, drag the fill handle down to apply the formula to other cells as needed.
Explanation and best practices:
- The TRIM function removes leading and trailing spaces, and reduces any extra spaces between words to a single space. This helps to prevent unwanted double commas if there are multiple spaces between words.
- The SUBSTITUTE function replaces each single space (the standard delimiter) with a comma and a space, adding clarity and consistency to the result.
- Since the formula only produces the formatted output, the original data in column A remains unchanged.
Add comma between words with VBA Macro
For users who frequently process large volumes of data or require advanced customization, a simple VBA macro can quickly add commas between words. VBA is especially useful for automating repetitive tasks, batch editing, or handling scenarios that formulas and built-in tools may not fully address.
1. Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications editor. In the editor, click Insert > Module, then enter the code below:
Sub AddCommasBetweenWords()
Dim WorkRng As Range
Dim Rng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If Rng.Value <> "" Then
Rng.Value = Replace(Application.WorksheetFunction.Trim(Rng.Value), " ", ", ")
End If
Next
End Sub2. After entering the code, click the 
button (Run) to execute. Select the range of cells you want to process when prompted, and the VBA macro will automatically add commas between each word in the selected cells.
Additional notes:
- Always save your workbook before running any VBA script, as the process will directly modify the source data.
- If the Developer tab is not visible, enable it via File > Options > Customize Ribbon.
- For advanced customization (e.g., different delimiters, conditional comma insertion), you can adjust the code accordingly.
You can use this macro to process large batches of data without repetitive manual replacement. This solution is particularly well-suited for users comfortable with basic programming and those who need to automate this task regularly.
Adding commas between words in Excel can be accomplished using several different methods, each tailored to distinct needs and levels of experience. Whether you use Find and Replace for a quick, straightforward update, Kutools for powerful and flexible batch processing, formulas for non-destructive dynamic results, or VBA for high-volume automation, you have options to suit most scenarios. When working with large datasets, always keep a copy of the original data and double-check the results for accuracy.
If you’re interested in exploring additional Excel solutions for advanced data processing, such as extracting keywords or converting delimiters, our website offers thousands of tutorials to help further enhance your productivity.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in