Abrar
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.
📁 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.
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.
- We will see that the data will be clear of that symbol. The result will look like the following image.
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.
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.
- 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.
- Thus, we will observe that the currency symbol has been removed from the output values. This is shown in the following image.
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)
- 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.
- Therefore, all the currency symbols of the Savings Amount will be removed in the image shown below.
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.
- The value in cell D6 will not have any currency symbol.
- Then, we drag the Fill Handle of cell D6 up to cell D13.
- Therefore, all the currency symbols of the Savings Amount will be removed like the image shown below.
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.
- 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.
- The outcome after removing the currency symbols are like the image below.
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.
- We select cell D6 and drag the Fill Handle up to cell D13.
- Thus, the new values in the D column will be free of currency symbols. This is shown in the following image.
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.
- In the Convert Text to Columns Wizard, we choose the Delimited option.
- Then, we check the Tab option and click on 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.
- So, the cells in the D column will have values without currency symbols like the following image.
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.
- As an outcome, we will observe that the remaining cells in the table are filled up with the numerical value free of currency symbol.
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.
- Then, we go to Insert and click on 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.
- 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.
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.
- 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.
- Thus, we will observe that the currency symbol has been added to the values. We have shown this in the following image.
📄 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.
