How to Remove Comma from Currency in Excel (6 Handy Tricks)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Remove Comma from Currency in Excel: 6 Handy Tricks

In this section, you will find 6 handy tricks to remove comma from currency in Excel. I will demonstrate them with proper illustrations. 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.

Dataset to remove Comma from currency in Excel

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.

Change Format to Remove Comma from Currency in Excel

  • Hence, your data will be formatted as General. As this format doesn’t contain comma in values, commas from your currency will be removed.


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.

Format Cells Dialog box to Remove Comma from Currency in Excel

  • 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.

Custom format to Remove Comma from Currency in Excel

  • 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

SUBSTITUTE function to Remove Comma from Currency in Excel

 💡 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.

Comma Removed from currency


Similar Readings


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.

Find and Replace Command to remove Comma

  • 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.

Apply Text to column Feature to Remove Comma

  • 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

Combination of Functions to Remove Comma in Excel

 💡 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)

CONCATENATE Function

  • 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.


Download Practice Workbook

You can download the practice book from the link below.


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.

Stay connected and keep learning!


Related Articles

<< Go Back To Remove Comma in Excel | Data Cleaning in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo