Dollar signs ($) in Excel are great for quickly identifying cells that contain monetary values. There are times, however, that it would be better to remove them.
In this article, I’ll show you four different ways to do this. Each approach has its pros and cons, so choose whichever fits your needs.
The first two methods will make the changes directly to the cells containing the dollar sign, while the last two will only extract the number from these cells and paste them on a different column.
Table of Contents
1. Changing the Cell’s Number Format
1. Select all the cells that contain the dollar sign.
2. Go to the Home tab and click on the small arrow next to the Number section.
If you prefer keyboard shortcuts, just press CTRL + 1.
This will open the Number Format menu.
3. Once the “Number Format” menu appears, choose either Number, Currency, or Accounting as the number format.
These 3 formats are practically the same, except that:
- The “Number” format doesn’t have any currency in it.
- The “Currency” and “Accounting” formats give the option to add a currency symbol.
If you select Number, you are given the option to configure the number of decimal places to show and to specify whether you’d like to use the comma as a separator for thousands or not. Choose whatever is necessary and click OK.
If you select either Currency or Accounting, select “None” as the Symbol and click OK.
You might be wondering — what’s the difference between the “Currency” and “Accounting” formats?
The answer? Text alignment.
If you select the Accounting format, the number will always be on the right side of the cell, no matter what. Even if you change the text alignment to left or center, the numbers will stay on the right side of the cell.
On the other hand, if you choose the Currency format, you can easily change the text alignment to either left, right, or center.
And that’s it! The dollar signs should now disappear from the selected cells.
2. Using Find and Replace
If changing the number format does not work, there’s a high chance that the cells you are working on are in text format.
To test this out, add this formula to an empty cell: =ISTEXT(D3)
Replace D3 with one of your ranges containing the dollar sign.
If the formula returns TRUE, then your cell is indeed being treated by Excel as a text.
If this is the case, the next best option would be to use Excel’s Find and Replace.
1. Highlight all the cells containing the dollar sign.
If you intend to remove the dollar sign from all cells inside the current worksheet, you may skip this step.
2. Go to the Home tab. From the Editing section, click the “Find & Select” button, and click Replace.
If you prefer a keyboard shortcut, you can press CTRL + H.
This should open the “Find and Replace” menu.
In the Find what field, type the dollar sign ($).
Leave the Replace with field blank.
NOTE: If there’s a space between the dollar sign and the number in your cells, remember to also add the space after the dollar signs in the Find what field.
Example: “$ “
3. If you intend to only replace the dollar sign in selected cells, ensure that you click on the Options << button.
The options should be hidden from the “Find and Replace” menu (as shown below).
On the other hand, if you plan to replace the dollar sign in all cells within the worksheet, click on the Options >> button to bring back the Find and Replace options (as shown below)
You should see the “Sheet” value in the Within field.
4. Once done, click on the Replace All button.
That’s it! The dollar signs should now be removed from your cells.
3. Using the Flash Fill
This next option is great if you prefer to have a separate column of cells containing the same values, but without the dollar sign.
1. Go to a blank column next to your data set.
2. Type the first number (but without the dollar sign).
3. Next, select the cell on the second data row.
4. Press CTRL + E to trigger the Flash Fill.
If done right, you should see Excel automatically filling up the remaining cells in that column.
If you have other columns to remove the dollar sign from, repeat the same steps on a different column.
IMPORTANT: You should perform the Flash Fill on a blank column nearest to the dataset. Otherwise, Excel might not recognize the number pattern you are trying to generate.
4. Using Excel Formulas
This last method still allows you to keep cells with dollar signs intact. It gives you the option to extract the numbers without the dollar sign.
1. On a blank cell, type either of the following formulas:
| FORMULA | NOTES |
| =VALUE(D3) | |
| =SUBSTITUTE(D3,”$”,””) | |
| =SUBSTITUTE(D3,CHAR(36), “”) | CHAR(36) represents the dollar sign. |
| =RIGHT(D3,LEN(D3)-1) | Use this if there’s no space between the dollar sign and the number (e.g., $100). |
| =RIGHT(D3,LEN(D3)-2) | Use this if there’s a single space between the dollar sign and the number (e.g., $ 100). |
Remember to change D3 with your actual cell.
2. Select the cell where you have added the formula.
Hover your mouse over the small green square on the right side of that cell. Wait until your mouse cursor turns into a black cross (as shown below).
3. Drag your mouse until you reach the last cell in that column.
This triggers Excel to copy the formula down to the remaining cells.
And that’s it! You should have a column containing the same values but without the dollar sign.
Conclusion
The dollar sign symbol inside cells is a great way to specify that the cells contain monetary values, and that they are in dollar currency. There are, however, instances when we need to remove them. I hope the steps described above will help you format the data the way you want them to be.