How to add comma between every single number in a cell of Excel?
Adding a comma between every single number in a cell in Excel can be a useful task, especially when formatting data for readability or further processing. This article provides a detailed guide on how to achieve this using formulas, VBA, and Kutools, along with optimization tips to enhance efficiency.
Add comma or other separator between every single number in a cell
Add comma or other separator between every single number with TEXTJOIN function
If you have Excel 2019 or later, you can use the TEXTJOIN function to insert commas between each digit.
1. Enter the following formula in a blank cell, if you’re using Excel 2019, press "Ctrl" + "Shift" + "Enter". (If you’re using Excel 356 or later versions, just press "Enter" key.)
2. Then, drag the formula down to fill it to other cells. Now you can see the numbers in the cell are separated by commas.
=TEXTJOIN(",", TRUE, MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1))
- "LEN(A2)": Counts the total number of characters in A2.
- "ROW(INDIRECT("1:"&LEN(A2)))": Creates an array of numbers from 1 to the length of A2.
- "MID(A3, ROW(...), 1)": Extracts each character from A2 based on the position numbers from the previous step.
- "TEXTJOIN(",", TRUE, ...)": Joins all extracted characters with a comma , as the separator.
Add comma or other separator between every single number with Kutools for Excel
Enhance your data formatting effortlessly with "Kutools for Excel", a powerful add-in designed to simplify complex tasks. If you need to add commas or any other separators between every single number in a cell, "Kutools" provides a quick and efficient solution.
After installing Kutools for Excel, click "Kutools" > "Text" > "Add Text" to go to the "Add Text" dialog box.
In the "Add Text" dialog box, configure the following settings:
- In the "Text" box, enter the comma or other separator you need;
- Select "Between characters" option from the "Only add to" drop down list;
- Finally, click "OK" Button.
Result: Kutools will automatically insert the specified separator between every single number in the selected cells. See screenshot:
Add comma or other separator between every single number with User Defined Function
This section provides a detailed walkthrough for creating and optimizing a UDF for this purpose.
- Press "Alt" + "F11" to open the VBA editor in Excel.
- In the editor, go to "Insert" > "Module" to create a new module.
VBA code: Add Comma Between Every Single NumberFunction AddCommas(inputText As String) As String 'Updateby Extendoffice Dim i As Integer Dim result As String For i = 1 To Len(inputText) result = result & Mid(inputText, i, 1) & "," Next i AddCommas = Left(result, Len(result) - 1) End Function - Close the code window, in a cell, type the following formula, and then, drag the fill handle down to fill this formula to other cells. Commas are added between every character as below screenshot shown:
=AddSeparator(A2)
By following these methods and optimization tips, you can efficiently add commas between every single number in a cell in Excel, enhancing both readability and data processing capabilities. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
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