8 Approaches to Remove Currency Symbol in Excel

There are different formats available for data in cells of Excel. We can apply them to a cell when we’re working with it. Currency format is one of them. In this format, we have a currency symbol along with any number. But sometimes we need the numerical value only without the currency symbol. In order to remove the currency symbol in Excel, we can follow multiple approaches. In this article, we will talk about different approaches of how to remove the currency symbol in Excel. Here is a brief overview of the approaches we will be taking and their outputs and formulas to remove currency symbol in Excel.

Overview of approaches by which we can remove currency symbol in Excel


📁 Download Excel File

Download the practice workbook below.


How to Remove Any Symbol in Excel

To remove any symbol in Excel, we can use the Find and Replace box. Let us assume that the dataset we will use today has a particular symbol within the data. The dataset with such a symbol is shown as follows.

sample dataset with asterisk symbol

To remove that symbol, we can briefly follow these steps.

⬇️⬇️ STEPS ⬇️⬇️

  • We use the keyboard shortcut Ctrl+H at first. A Find and Replace box will appear.
  • Put the character we want to remove in the Find In this case, we cannot put an asterisk as it is a wildcard. So, to remove asterisk, we write ~* in the Find box.
  • We keep the Replace box empty.
  • Now, we press Replace All and Close the window.

Replacing asterisk symbol with a blank character

  • We will see that the data will be clear of that symbol. The result will look like the following image.

Output after removing asterisk symbol

In this way, any symbol can be removed from the contents of cells. However, if a symbol such as a currency symbol exists, then we can use the Format Cells option or different in-built functions and other features.

Learn to Remove Currency Symbol in Excel with These 8 Easy Approaches

The currency symbol in Excel is associated with the currency formatting of cells. We cannot remove it using the conventional Find and Replace box. To demonstrate the approaches, we will consider a dataset that has different people with their savings amounts in the currency format. We will use this dataset to remove currency symbol in Excel in 8 unique ways. The dataset is presented below.

Sample dataset

Approach

1. Change Format of Cells to Remove Currency Symbol

In this approach, we will change the format of the output cells to remove the currency symbol within data in Excel. We can access the Format Cells option either by right-clicking or by applying the shortcut key Ctrl+1. We have broken down this approach into the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select the cell range C6:C13.
  • After that, we press the shortcut key Ctrl+1.

Accessing Format cells through shortcut key

  • A new Format Cells window will appear.
  • We move to the Currency section under the Number tab.
  • In the Symbol window, we select the option named None.
  • We set the value of the Decimal places to zero (0) and press OK.

Format Cells window

  • Thus, we will observe that the currency symbol has been removed from the output values. This is shown in the following image.

Remove currency symbol by changing cell format

Approach

2. Remove Currency Symbol Using Excel VALUE Function

The VALUE function in Excel gives a number equivalent output of a text string. So, since the cell values in our dataset with a dollar symbol in the currency format, the VALUE function will give a number equivalent output of the argument. The syntax of VALUE function is as follows.

Syntax and Arguments

VALUE(Text)

Text – This argument is a required argument. It is the reference to the text string that is to be converted to a number output.

To remove the currency symbol using this approach, we follow these steps.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell D6.
  • Then, we go to the formula bar and write down the following formula.
=VALUE(C6)

VALUE function to remove currency symbol

  • After that, we press Enter. The value in cell D6 will not have any currency symbol.
  • Then, we drag the Fill Handle of cell D6 up to cell D13.

Dragging Fill Handle

  • Therefore, all the currency symbols of the Savings Amount will be removed in the image shown below.

Remove currency symbol by using VALUE function

Approach

3. Discard Currency Symbol with SUBSTITUTE Function

The SUBSTITUTE function replaces a certain value instead of a present value. The syntax of this function is as follows.

Syntax and Arguments

SUBSTITUTE(text, old_text, new_text, [instance_num])

text – represents the cell in which the text is present.

old_text – is the old text that we want to replace.

new_text – is the newly replaced text.

instance_num – specifies the occurrence of old text that we want to replace with new text.

Here, we will substitute the currency symbol with a blank to remove the currency symbol.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, we select cell D6.
  • Next, we go to the formula bar and write down the following formula, and press Enter.
=SUBSTITUTE(C6,"$","")

🔨 Formula Breakdown

SUBSTITUTE(C6,”$”,””)

👉  Here, the first argument C6 represents the cell from where we need to find the value for substitution.

👉  The second argument “$” indicates the value or string to be replaced.

👉  The third argument “” denotes character that is to be the newly replaced value. There is no character within the inverted comma.

SUBSTITUTE function to remove currency symbol

  • The value in cell D6 will not have any currency symbol.
  • Then, we drag the Fill Handle of cell D6 up to cell D13.

Dragging Fill Handle

  • Therefore, all the currency symbols of the Savings Amount will be removed like the image shown below.

Remove currency symbol by using SUBSTITUTE function

Approach

4. Combination of RIGHT & LEN Functions to Delete Currency Symbol

We can use a combination of RIGHT and LEN functions evidently to remove currency symbols as well. The LEN function shows the number of characters present in a string. The RIGHT function gives an output consisting of a number of characters that have been specified in the function as an argument.

The syntax of LEN function is as follows:

Syntax and Arguments

LEN(text)

text – The text whose length we are looking for.

The syntax of RIGHT function is as follows:

Syntax and Arguments

RIGHT(text,[num_chars])

text – Text string from which we want to extract certain characters.

num_chars – The number of characters that we will extract from the right.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, we select cell D6.
  • Then, we go to the formula bar and write down the following formula.
= RIGHT(C6,LEN(C6))

🔨 Formula Breakdown

RIGHT(C6,LEN(C6))

👉  LEN(C6) gives an output of the total character number present in the string in cell C6 value.

👉  The first argument in the RIGHT function is C6, where we can find the value from which we will extract characters.

👉  The second argument LEN(C6) denotes the number of characters to extract from the right of a string.

RIGHT and LEN function to remove currency symbol

  • Then, we press Enter. We will see that the output in cell D6 will not have any currency symbol.
  • Now, we drag the Fill Handle of cell D6 up to cell D13.

Dragging Fill Handle

  • The outcome after removing the currency symbols are like the image below.

Remove currency symbol by using RIGHT and LEN functions

Approach

5. Use of REPLACE Function to Erase Currency Symbol

We can use REPLACE function to replace a part of a string with new characters. In this approach, we will use this function and its arguments to erase the currency symbol in Excel data. The syntax of the REPLACE function is presented below.

Syntax and Arguments

REPLACE(old_text, start_num, num_chars, new_text)

old_text – is the previous string that we are trying to replace.

start_num – indicates the character position from where we want to start replacing the old string.

num_chars – represents the number of characters we will replace starting from the start_num.

new_text – is the new text string over the old one.

The use of REPLACE function to erase the currency symbols is explained by the following step-by-step procedure.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, we select cell D6.
  • Then, we go to the formula bar and write down the formula below and press Enter.
=REPLACE(C6,1,0,"")

🔨 Formula Breakdown

REPLACE(C6,1,0,””)

👉  The first argument C6 represents the location of the value which we want to replace.

👉  1 represents the position of the character from where we want to replace the old string. That is the first character to be replaced.

👉  0 denotes the number of characters we are replacing and “” is the replaced character.

REPLACE function to remove currency symbol

  • We select cell D6 and drag the Fill Handle up to cell D13.

Dragging Fill Handle

  • Thus, the new values in the D column will be free of currency symbols. This is shown in the following image.

Remove currency symbol by using REPLACE function

Approach

6. Use Text to Columns Feature to Separate Currency Symbol

In this approach, we will be using the Text to Column feature under the Data tab to remove the currency symbol in Excel.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, we select cell range C6:C13.
  • Then, we go to the Data tab and click on the Text to Columns feature.

Text to Columns approach to remove currency symbol

  • In the Convert Text to Columns Wizard, we choose the Delimited option.

Select the Delimited option

  • Then, we check the Tab option and click on Next.

Check the Tab option and press Next

  • We select the Column data format as General and on the Destination box, we write this cell reference.
=$D$6:$D$13
  • Now, we click on the Finish button.

Select data format and destination

  • So, the cells in the D column will have values without currency symbols like the following image.

Text to Column feature to remove currency symbol

Approach

7. Eliminate Currency Symbol by Flash Fill

The Flash Fill command can sense patterns and can fill data automatically. We can use this command to eliminate currency symbol in Excel. Now, we will follow the steps below to remove currency symbol in Excel using Flash Fill command.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, we select cell D6 and write down the number present in cell C6, that is, 5991.
  • Then, we go to the Data tab and from the Data Tools section, we click on the Flash Fill command.

Flash fill feature to remove currency symbol

  • As an outcome, we will observe that the remaining cells in the table are filled up with the numerical value free of currency symbol.

Flash Fill

Approach

8. Use a VBA Code to Remove Currency from a Large Dataset

The fastest way to remove currency symbol of a large amount of data is to use a VBA code. We can do so by following these steps.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we right click on our VBA Code worksheet and click on View Code.

Select View Code to access Visual Basic editor

  • Then, we go to Insert and click on Module.

Insert Module

  • In this way, we will create a new module. In the new module, we write down the following VBA code.
Sub RemoveCurrencySign()
'RemoveCurrencySign Macro
Range("C6:C13").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
End Sub
  • Now, we press the Run button.

Run the VBA code

  • As a result, we will see that the currency symbol will be removed from the selected range of cells in the VBA code. The output image will look like the following one.

VBA code result for removing currency symbol


How to Add Currency Symbol in Excel

To add currency symbol in Excel, we can format the cells to a Currency format according to our needs. We can do this by following the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select the cell range C6:C13.
  • Next, we press the shortcut key Ctrl+1.

Accessing Format Cells window through shortcut

  • A new Format Cells window will appear.
  • We move to the Currency section under the Number tab.
  • In the Symbol window, we select the option named $ English (United States).
  • We set the value of the decimal place to zero (0) and press OK.

Format Cells window

  • Thus, we will observe that the currency symbol has been added to the values. We have shown this in the following image.

Adding a currency symbol


📄  Important Notes

🖊️  The Find and Replace command will not be useful when the currency sign is not present in the string as a character.

🖊️  We can access the Format Cells option either by right clicking to bring the Context Menu or by using the shortcut key Ctrl+1.


📝  Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌  We can change the cell format to remove currency symbol in Excel.

📌  Moreover, the use of different functions like VALUE, SUBSTITUTE, RIGHT, LEN, REPLACE and their combinations can make us remove currency symbols from a dataset.

📌  Additionally, we can use Data Tools such as Flash Fill and Text to Columns feature to remove currency symbols as well.

📌  A simple VBA code also provides the solution to removing currency symbols in Excel.


Conclusion

To conclude, I hope that this article has helped you to understand how to remove and add currency symbol in Excel. If you have any queries, reach out to us by leaving a comment below. In addition, follow our website ExcelDen for more informative articles related to Excel.

(Visited 27 times, 1 visits today)
Abrar

Abrar

Hello there! I am Mohammad Abrar Uddin, an engineering graduate from BUET. I write articles and blogs related to Excel in this site and hope that the viewers can learn simple to complex solutions of Excel problems easily by reading such articles.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo