- Trending Categories
- Data Structure
- Operating System
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to add comma between every single number in a cell of Excel?
When you are dealing with a large number of cells within the data for your spreadsheet, it may be a good idea to manually insert commas in certain locations within individual cells. When you work with the numbers in a list that was randomly formed in Excel, it is extremely common for there to be no commas in the data. This is because Excel does not insert commas automatically. This is especially important to keep in mind in circumstances in which the data is transferred from word editors. But when you have to deal with a large amount of data, you have to figure out a better way to do it or devise a different technique to do it manually. Either way, you have to find a solution.
In this tutorial we are going to learn to add comma between every single number.
Add comma Using Substitute function
Excel's primary method for distinguishing the various lengths of numbers hundreds, thousands, millions, etc., the comma style, is utilized for writing numbers. Users are able to read and spell the numerals in their erroneous form because to this.
Excel's SUBSTITUTE function has the ability to replace text based on a match. If you want to replace text based on matching rather than location, you should make use of the SUBSTITUTE function. You have the option of specifying the specific instance of found text that should be replaced. If you want to replace an old text with a new one, this function will be quite helpful for you to employ. It replaces particular text in a string of text, means it inserts new content in place of the previous text in a string of text.
Let’s see how to use SUBSTITUTE function to separate every numbers by comma through an example step by step
In our example, we have some numbers in an excel sheet. See the image given below.
Select the cell next to the numbers and add the following formula to it. In our case we have selected B2 cell
In our example A2 is the cell in which the numbers are written. You can use the cell number as per your requirement.
In above formula, the SUBSTITUTE function will replace all the space in between the numbers with comma.
See the image given below.
To reflect this formula in other cells, select the B2 cell and drag to the specified column below B2 cell and you will get the required result.
See the image given below.
In this tutorial you learnt about the use of SUBSTITUTE function in the case you want to separate the numbers with comma separator.
- How to add comma at the end of cell/text in Excel?
- How to add comma before number in Excel?
- How to Add a Comma after the First Word in Each Cell in Excel?
- How to Add Semicolon to Each Cell in Excel?
- Add text and number into specified position of cell in Excel
- How to Add a Hyperlink to a Specific Part of a Cell in Excel?
- How to Add Periods to The End of Cell Contents in Excel?
- How to Automatically Copy a Cell to Clipboard with a Single Click in Excel?
- How to activate cell for editing with single clicking in Excel?
- How to add 1 to a specified cell if the cell contains certain text in Excel
- How to add a character before each word in a cell in Excel
- How to add check mark in a cell with double clicking in Excel
- How to add a single data point in an Excel line chart?
- How To Add Text Cells Together Into One Cell In Excel?
- How to add hyphen between words in Excel?