Alok Paul
We work in Excel for different purposes. Like, we work with currency-related operations, and there appear currency signs. And you could imagine a lot more cases of appearing different signs. But sometimes, we may need to remove these signs. So, we will discuss how to remove the sign in Excel here in this article.
Download Practice Workbook
Download the following practice workbook to exercise while you are reading this article.
3 Examples to Remove Sign in Excel
Commonly we face unusual Dollar ($), Plus(+), Minus(-), Percentage (%) signs that appear in Excel. We will show now how to remove those signs.
1. Remove Dollar Sign in Excel
The Dollar ($) sign appears in Excel in two formats. One is- it can be attached with numeric data, like currency or accounting data. Another way, it can be presented in Excel in Text format. We will show how to remove both forms of the dollar sign.
1.1 Using Format Cell Feature
We will use the Format Cells feature to remove the Dollar($) sign from Excel here.
Steps:
- First of all, choose all cells of the Salary column.
- Then press the right button of the mouse.
- Next, choose the Format Cells option from the Context Menu.
Note: We can use the keyboard shortcut Ctrl+1 to avail Format Cells option.
- Then enter the Format Cells window.
- This column is formatted as Accounting.
- Finally, we just change the Symbol from the Dollar ($) sign to None and then press OK.
- Look at the worksheet now.
The Dollar($) sign has been removed from the worksheet.
- Again, you will find Dollar($) signs in Currency format too. Same way, just select None as the Symbol.
Read More: How to Remove Pound Sign in Excel (8 Easy Methods)
1.2 Using VALUE Function
We can use the VALUE function here to remove the Dollar sign. Follow the steps below.
The VALUE function converts a text string that represents a number, to a numeric format.
Steps:
- Add a new column to show the value without the Dollar($) sign.
- Go to the Cell E4 of the new column and put the following formula.
=VALUE(D4)
- Press the Enter button to get the result.
- Now, drag the Fill Handle icon downwards.
Read More: How to Remove Sign from Numbers in Excel (6 Suitable Methods)
1.3 Combining SUBSTITUTE & CHAR Functions
The SUBSTITUTE function replaces existing text with a new text in a text string.
The CHAR function returns the character specified by the code number from the character set for your computer.
We will use the combination of the SUBSTITUTE and CHAR functions here that will remove the Dollar($) sign.
Steps:
- Go to Cell E4 of the Salary($) column.
- Put the following formula based on the combination of SUBSTITUTE and CHAR functions.
=SUBSTITUTE(D4,CHAR(36),"")
- Now, drag the Fill Handle icon downwards.
The Dollar ($) sign was removed in the new column.
1.4 Merging RIGHT & LEN Functions
The RIGHT function returns the specified character-number from the rightest end of the text string.
The LEN function returns the character number in a text string.
If the above-mentioned methods do not work that means the Dollar($) sign is present in the data in text format. Usually, the data is left-aligned when data is in text form.
Steps:
- Enter Cell E4 and simply put the formula.
=RIGHT(D4,LEN(D4)-1)
- Drag the Fill Handle icon downwards.
The Dollar sign was removed successfully.
1.5 Using REPLACE Function
We can also remove dollar signs using the REPLACE function.
The REPLACE function replaces the part of a text string with a different text string.
Steps:
- Copy and paste the following formula on Cell E4 based on the Replace function.
=REPLACE(D4,1,1,"")
- Pull the Fill Handle icon downwards.
1.6 Applying Find and Replace Tool
In this section, we will use the commonly used Find and Replace Tool. This method requires no extra column to get the result without the Dollar($) sign.
Steps:
- First, press Ctrl+H to get the Find and Replace window will appear.
- Put the Dollar($) sign on the Find what box and keep the Replace with box blank.
- Finally, press Replace All button.
- Look at the worksheet.
The Dollar($) sign is removed from the worksheet. Also, a dialog box appears that shows the number of replacements. Press OK on that box and close the Find and Replace feature.
1.7 Using Flash Fill
In this section, we will use Flash Fill. This Flash Fill follows a pattern taken from the adjacent column. And spread this pattern to the selected range.
Steps:
- Our main data is in the Salary column. We will apply Flash Fill on Salary($) column.
- Fill up the first two cells of the Salary($) column by values taken from the Salary column without the dollar sign.
- Go to the Data tab.
- Choose the Flash Fill option from the Data Tools group.
Note: We can also avail of the Flash Fill feature using the keyboard shortcut Ctrl+E.
- Now, look at the worksheet.
After applying the Flash Fill, Dollar($) sign has been removed from the data.
Read More: How to Remove Symbol in Excel (8 Ways)
2. Remove Plus (+) or Minus (-) Sign Using SUBSTITUTE Function
We will show how to remove the Plus (+) and Minus (-) signs easily from the data here. The SUBSTITUTE function will use for this operation. We have a dataset of some random data for this purpose.
Steps:
- Go to Cell C4 and put the following formula.
=SUBSTITUTE(B4,"+","")
- Double click the Fill Handle icon.
We can see all plus signs have been removed from the data. Also, data are left-aligned. But we want to make the data right aligned. For that need to add a “Zero” (0) in the formula.
- Modify the formula of Cell C4. So, the formula becomes:
=SUBSTITUTE(B4,"+","")+0
- Again, double-click the Fill Handle icon.
Our data have the right alignment. We showed this as we know numeric data are right-aligned and text data are left-aligned.
Similarly, we can remove minus signs. And that the formula will be like:
=SUBSTITUTE(B4,"-","")+0
3. Remove Percentage (%) Sign
In this method, we will show how to remove the Percentage(%) sign from Excel. The percentage is used to present a fraction value multiplied by 100 for better understanding. We can remove the Percentage(%) sign by changing the cell format. We have a dataset of some employees with their yearly increments.
Steps:
- Choose all cells containing the percentage sign. Go to the Number group from the Home tab.
- Press the Arrow on the lower bottom side of the Number group.
- The Format Cells window appears.
- Select General from the Number Then, press OK.
Note: We can use the keyboard shortcut Ctrl+1 to avail Format Cells option.
- Now, look at the worksheet.
The percentage sign has been removed successfully.
Read More: How to Remove Percentage Symbol in Excel Without Changing Values
Conclusion
In this article, we showed how to remove different signs from Excel. Some methods may apply to multiple signs. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.
