Does comma in numbers in an Excel worksheet bother you? Do you want to clean up your data by removing the comma from the number describing currency? Are tired of manually removing the comma from your database? Hang on there! I’ve got you. In this article, I will show you how to remove comma from currency in Excel.
Download Practice Workbook
You can download the practice book from the link below.
6 Tricks to Remove Comma from Currency in Excel
In this section, you will find 6 handy tricks to remove comma from currency in Excel. I will demonstrate them one by one here. Let’s check them now!
1. Changing Number Format
Let’s say, we have got a dataset of some salesmen and their monthly sales for a certain period of time.
You can see that the numbers in the sales column are in Currency format and the digits are separated by commas (thousand separators). Our aim is to remove commas from this number of currency format. In order to do so, proceed with the following steps.
Steps:
- First of all, select the range of the data, go to the Home tab> click Number Format.
- Here, both the General and Number option doesn’t include a comma in their values. Select one of them. I’ve selected General.
- Hence, your data will be formatted as General. As this format doesn’t contain comma in values, commas from your currency will be removed.
Read More: How to Remove Comma Between Numbers in Excel
2. Using Format Cells Dialog Box
We will now remove the comma from the currency for our same dataset using the Format Cells dialog box. We will approach this in two ways. Let’s check them out.
2.1. Unmarking Thousand Separator
The currency format and some other formats contain commas such as Decimal Separator and Thousand Separator. You have just to disable this separator. Follow the steps below.
Steps:
- Firstly, select the range of data and open the Format Cells dialog box. The keyboard shortcut to open the Format Cells dialog box is CTRL+1.
- In the dialog box, select Number from Category from the Number icon and Unmark the Use 1000 Separator. In the Sample field, you can see the changed number format. Click OK.
- Finally, Excel will change the currency format and remove comma from the data.
So easy, isn’t it?
2.2. Applying Custom Format
In the previous methods, we have changed the Number Format which doesn’t keep the dollar ($) symbol. But if you want to keep the symbol, you have to apply Custom Format for this. In order to do so, just proceed with the steps below.
Steps:
- First of all, open the Format Cells dialog box by pressing CTRL+1.
- From the Number tab, select Custom from Category.
- In the Type field, you will see that the symbol ($#,##0) is showing a comma.
- Here, just remove the comma and in the sample field, you can see the data without comma.
- Now, you can see that the cells are showing data without comma but showing the dollar sign.
3. Using SUBSTITUTE Function
Now we will show the use of the SUBSTITUTE Function to remove comma from currency. This function replaces the existing text with a new text in a text string. So, let’s start the process like the one below.
Steps:
- First of all, create a new column> select a cell and type the following formula in the selected cell.
=SUBSTITUTE(C5,",", " ")
Here,
- C5 = the cell containing data from which you want to remove the comma
💡 How the Formula Works
The SUBSTITUTE Function finds comma (“,”) withing cell C5 and replace the comma by nothing as the third argument contains nothing in the inverted comma (“ ”).
- Then, press ENTER, and your cell will show the result. The cell has removed comma from its value.
- Now, use the Fill handle tool to Autofill the formula to the next cells.
- After that, all the cells will remove the comma from the value.
Read More: How to Remove Comma in Excel Using Formula (2 Ways)
4. Using Find and Replace Command
If your dataset includes commas in some data but is in Text format, in this case, Find and Replace is one of the best features.
Follow the steps to apply this feature.
Steps:
- At first, press CTRL+F to open the Find and Replace dialog box.
- Here, in the Replace icon, type comma (,) in the Find What field and keep the Replace with field blank> Now, click Replace All.
- As a result, all the commas will be removed.
5. Utilizing Text to Column Feature
Excel allows a user to utilize the Text to Column feature to remove commas from data. In order to do so, follow the steps.
Steps:
- Firstly, select the data range, go to the Data tab> and click Text to Column from the Data Tools group.
- Then the Convert Texty to Columns Wizard (Step 1 of 3) will appear.
- Here, select Fixed width> click Next.
- Click Next at Step 2 also.
- After that, in Step 3, click Advanced.
- Now, the Advanced Text Import Settings pop-up will appear. Put a comma in the Decimal separator field> click OK.
- Later, click Finish to close the Wizard.
- Finally, you will get the comma removed from your data.
6. Using a Combination of Functions
In this section, we will use a combination of functions to create a formula that will remove comma from currency. We will use the SEARCH, LEFT, RIGHT, and CONCATENATE Functions for this purpose.
Steps:
- First, create a column for putting the digits before the comma. Type the following formula in the first cell.
=LEFT(C5,SEARCH(",",C5)-1)
Here,
- C5 = the cell containing data from which you want to remove the comma
💡 How the Formula Works
The SEARCH Function finds the position of the comma within cell C5. So, it returns 3.
LEFT(C5,3-1)=LEFT(C5,2) gives the 2 characters from left.
So, Output-> 26.
- Then, press ENTER and the cell will give you the result. Drag the formula down.
- So, all the cells you have copied the formula will show you the output.
- Now, apply the following formula to another column for putting the digits after the comma.
=RIGHT(C5,LEN(C5)-SEARCH(",",C5))
Here,
- C5 = the cell containing data from which you want to remove the comma
💡 How the Formula Works
The SEARCH Function finds the position of the comma within cell C5. So, it returns 3.
The LEAN Function results in the total number of characters within C5. Output-> 6.
Finally, RIGHT(C5,6-3)=RIGHT(C5,3) returns the characters after the comma.
So, Output-> 354.
- Then, drag the formula for all cells.
- Now, we have to concatenate the section before the comma and after the comma.
- So, apply the following formula to concatenate this two-part.
=CONCATENATE(D5,E5)
- Finally, copy the formula to all cells and you will get the final output.
♦♦Note: The output may perform as text, to let it work as a number you may need to utilize the VALUE function to wrap up the formula stated here.
Conclusion
In this article, I have tried to show you some methods to remove comma from currency in Excel. I hope this article has shed some light on your way to this task. If you have better techniques, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Have a great day.
Stay connected!