Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
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
Step 1
In our example, we have some numbers in an excel sheet. See the image given below.
Step 2
Select the cell next to the numbers and add the following formula to it. In our case we have selected B2 cell
=SUBSTITUTE(A2," ",",")
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.
Step 3
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.
Conclusion
In this tutorial you learnt about the use of SUBSTITUTE function in the case you want to separate the numbers with comma separator.
4K+ Views
