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 before number in Excel?
Adding commas manually in specific places within cells may be a decent idea when you deal with numerous cells inside your spreadsheet data, but when you deal with a vast amount of data, you have to discover a better approach to do it or come up with a different way to do it manually.
When you merely want to display the currency symbols together with the totals for the columns or rows, use the Comma in a huge table of financial data. This format is called the "Comma Style." You can format the remaining data with the Comma Style, and the decimal points will align with the totals that utilize the accounting format. The right parenthesis in negative numbers is accommodated by the space that is left between the last digit and the boundary of the cell on the right. This ensures that the right parenthesis also align precisely on the decimal point.
In this tutorial, you will learn how to add comma before a number through some examples.
Step 1
In our example, there are some employee name and employee ID in a cell. We want to separate the employee name and employee Id with comma.
Step 2
Select the blank cell next to the list. In this example, we are selecting cell B2 and adding the following formula to it.
=REPLACE(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),1,","&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),1))
In our example, the cell number is A2. So we have written A2 in this formula.
This formula makes use of the REPLACE, MIN, MID, and FIND functions in order to append a comma prior to the first number contained within a cell.
The REPLACE function changes the position of characters in a text string. When the location of the text to be changed is known or easy to find, you can use the REPLACE function.
The MIN function finds the least possible numeric value inside the given input and returns it. Any kind of numeric information can be utilized in conjunction with the MIN function to obtain the lowest possible number.
The MID function takes a certain number of characters from the middle of a string of text that you give it. There are three required arguments for MID.
The FIND function gives back a number that shows where one text string is inside another. If the search string shows up more than once, FIND tells you where the first time it shows up.
After entering the above formula, when you press Enter, you will get the output as the text separated from numbers with comma separator. See the following image.
Step 3
To get reflected in other cells, click the "+" sign that appears on the lower-right corner of the cell B2, which activates the autofill function and then drag down.
Conclusion
In this tutorial, you learnt how to insert comma before the first number in a cell by using REPLACE, MIN, MID, and FIND functions.
791 Views
