How to Remove Dollar Sign in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Working in a pre-formatted dataset we encounter dollar signs ($) preceding the values. In some cases, we don’t need to insert any dollar sign ($) at all. To remove the dollar sign, in this article, we use multiple functions such as RIGHT, LEN, VALUE, SUBSTITUTE, and REPLACE as well as multiple Excel Features.

Let’s say, we have a pre-formatted dataset to show dollar sign ($) before any Price value.

Dataset-How to Remove Dollar Sign in Excel


Remove Dollar Sign in Excel:7 Easy Ways

Method 1: Format Cell Feature to Remove Dollar Sign

Excel offers various features to remove dollar signs preceding values. Format Cells feature is one of the convenient ways to go with it.

Step 1: Select the whole Range (i.e., D4:E18) you want to remove the dollar sign from.

Step 2: Right-click on any selected cells then choose Format Cells from the options.

Format cells-How to Remove Dollar Sign in Excel

Step 3: Format Cells window pops up. You can see the values set as Currency and Symbol as Dollar ($). Change the Symbol to None then Click OK.

Format cells symbol

You can bring up the Format Cells dialog box by pressing the keyboard shortcut CTRL+1.

The whole selected range will convert as a dollar signless version of the original dataset similar shown in the image below.

Format cells result-How to Remove Dollar Sign in Excel

Read More: How to Remove Currency Symbol in Excel


Method 2: RIGHT LEN Function to Remove Dollar Sign

As the dollar sign precedes all the values, we can use the RIGHT and LEN functions to fetch all the characters except the dollar sign. In order to use the RIGHT function, we have to transform the values along with the dollar sign as Text.

Step 1: Put an apostrophe () before the values (with the dollar sign) in the cells.

Right Len function-How to Remove Dollar Sign in Excel

Then repeat the step pressing ENTER for every cell. You’ll get a dataset like in the following picture.

Right LEN function

To convert every cell as text is a hideous work. In the case, you have a very large dataset, I won’t recommend you to use this method.

Step 2: Paste the following formula in any adjacent cells (i.e., F4)

=RIGHT (E4, LEN(E4)-1)

 In the formula.

LEN(E4)-1; pass the total character length by subtracting 1 character.

RIGHT (E4, LEN (E4)-1); returns all the characters from the right.

Right Len formula

Step 3: Hit ENTER and Drag the Fill Handle, then it will remove the dollar signs ($) from each cell.

Right LEN final result


Method 3: Using Find and Replace Feature to Remove Dollar Sign

The Find and Replace feature also requires values to be in text format. By repeating Step 1 of Method 2 we can transform the values as text.

Step 1: After transforming all the values as Text, go to Home Tab > Find & Select (In Editing section) > Select Replace.

Find and Replace -How to Remove Dollar Sign in Excel

Step 2: Find and Replace dialog box appears. In the dialog box, Insert the dollar sign ($) in the Find What dialog box. Keep the Replace With box Empty. Then Click on Find All. All the cells containing the dollar sign ($) pile up in the lower part of the Find and Replace dialog box.

Find and Replace dialog box

Step 3: Afterward, Click on Replace All and close the Find and Replace dialog box.

Find and Replace Replace All

Execution of the steps results in a similar image to the following image.

Find and Replace Final Result-How to Remove Dollar Sign in Excel


Method 4: Using VALUE Function to Remove Dollar Sign

You can use the VALUE function to remove the dollar sign. VALUE works in a way to extract values from the text. The syntax of the VALUE function is

VALUE (text)

Therefore, the values are required to be in text formation. We repeat Step 1 from Method 2 for only the TotalPrice column.

Step 1: Type the below formula in any blank cell (i.e., F4).

 =VALUE(E4)

VALUE formula-How to Remove Dollar Sign in Excel

 Step 2: Tab ENTER and Drag the Fill Handle, you’ll see all the dollar signs in values get omitted.

VALUE formula-How to Remove Dollar Sign in Excel

Related Content: How to Remove Dollar Sign in Excel Formula


Method 5: Using SUBSTITUTE Function to Remove Dollar Sign

Another function worth mentioning, to remove the dollar sign (other signs, characters as well), is the SUBSTITUTE function. The syntax of the function is

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

Inside the function

text; refers to any text or the cell reference.

old_text; defines the text you want to replace.

new_text, is the text you want to replace the old_text with.

[instance_num]; the occurrence of old_text you want to replace.

Step 1: Write the following formula in cell E4.

 =SUBSTITUTE(E4,CHAR(36),””)

Here,

E4; is the cell reference.

CHAR (36); is the old_text for dollar sign ($).

“”; results in the new_text as nothing.

Substitute function-How to Remove Dollar Sign in Excel

 Step 2: Hit ENTER then Drag the Fill Handle, all the entries transform into dollar signless values.

Substitute function-How to Remove Dollar Sign in Excel

If you want to remove the dollar signs in the UnitPrice column also, just repeat Steps 1 and 2 for an additional column.


Method 6: Using REPLACE Function to Remove Dollar Sign

You can also use the REPLACE function to do the task. The syntax of REPLACE is

REPLACE (old_text, start_num, num_chars, new_text)

In brief,

old_text; refers to the text in which you want to replace any character.

start-num; defines the position of the character inside the old_text you want to replace.

num_chars; defines the number of characters inside the old_text you want to replace.

new_text; is the text or character you want the old_text character to replace with.

Since old_text in the REPLACE function can only be in text, we have to transform the values to text following step 1 in Method 2. For convenience, we just transform only the TotalPrice column and show the outcomes for only one column.

 Step 1: Insert the below formula in cell F4.

=REPLACE(E4,1,1,””)

Here,

E4; is the old_text.

1; is the start_num.

1; is the num_chars.

“”; is the new_text.

Replace function-How to Remove Dollar Sign in Excel

Step 2: Hit ENTER after that Drag the Fill Handle, by doing so, you’ll get all the dollar signs ($) removed from the values.

Replace function result-How to Remove Dollar Sign in Excel

Read More: How to Remove Pound Sign in Excel


Method 7: Using Flash Fill to Remove Dollar Sign

Flash Fill is a handy feature to mimic the values entered once previously in Excel.

Step 1: Enter one or two first values without the dollar sign in cells. For better understanding, we execute only one column (i.e., TotalPrice).

Flash Fill feature-How to Remove Dollar Sign in Excel

Step 2: Select the whole range then go to Data Tab > Flash Fill (in Data Tools section)

Flash Fill

In a moment Excel will mimic all the values previously entered but not with the dollar sign ($) similar to the following picture.

Flash Fill final result-How to Remove Dollar Sign in Excel


Dataset for Download


Conclusion

In the above article, we use functions such as RIGHT, LEN, VALUE, SUBSTITUTE, and REPLACE as well as multiple features. In many cases, to apply the function we have to convert the values into text strings. If you find this redundant, ignore the methods as we discussed them only to show their capabilities. On another note, to keep things simple, in several methods, we transform only one column. You can apply the methods to as many columns as you want. Hope you find these above methods worthy of your quest. Comment, if you have further inquiries or have anything to add.


Related Articles


<< Go Back to Remove Symbol in Excel | Excel Symbols | 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.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo