Maruf Islam
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 for Download
7 Easy Ways to Remove Dollar Sign in Excel
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.
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.
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.
You can bring up the Format Cells dialog box by pressing keyboard shortcuts CTRL+1.
The whole selected range will convert as a dollar signless version of the original dataset similar sown in the image below.
Read More: How to Remove Specific Characters in Excel ( 5 Ways)
Method 2: RIGHT LEN Function to Remove Dollar Sign
As the dollar sign precedes all the values, we can use the RIGHT function 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.
Then repeat the step pressing ENTER for every cell. You’ll get a dataset like in the following picture.
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)
In the formula.
LEN(E4)-1; pass the total character length subtracting 1 character.
RIGHT (E4, LEN (E4)-1); returns all the characters from the right.
Step 3: Hit ENTER and Drag the Fill Handle, then it will remove the dollar signs ($) from each cell.
Related Content: Excel Remove Characters From Right (5 ways)
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.
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.
Step 3: Afterward, Click on Replace All and close the Find and Replace dialog box.
Execution of the steps results in a similar image to the following image.
Read More: Remove Characters after a Specific Character in Excel (4 Tricks)
Similar Readings:
- How to Remove Dashes in Excel (3 Methods)
- Remove Single Quotes in Excel (6 Ways)
- How to Remove Parentheses in Excel (4 Easy Ways)
- Remove Last Character from String Excel (5 Easy Methods)
- How to Remove Character from String in Excel (14 Ways)
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
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).
Step 2: Tab ENTER and Drag the Fill Handle, you’ll see all the dollar signs in values get omitted.
Related Content: Remove Specific Character from String Excel (5 Methods)
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
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.
Here,
E4; is the cell reference.
CHAR (36); is the old_text for dollar sign ($).
“”; results in the new_text as nothing.
Step 2: Hit ENTER then Drag the Fill Handle, all the entries transform into dollar signless values.
If you want to remove the dollar signs in the UnitPrice column also, just repeat Steps 1 and 2 for an additional column.
Related Content: How to Remove Spaces in Excel: With Formula, VBA & Power Query
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
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.
Here,
E4; is the old_text.
1; is the start_num.
1; is the num_chars.
“”; is the new_text.
Step 2: Hit ENTER after that Drag the Fill Handle, by doing so, you’ll get all the dollar signs ($) removed from the values.
Read More: How to Remove Last Character in Excel(Easiest 6 Ways)
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).
Step 2: Select the whole range then go to Data Tab > Flash Fill (in Data Tools section)
In a moment Excel will mimic all the values previously entered but not with the dollar sign ($) similar to the following picture.
Related Content: VBA to Remove Characters from String in Excel (7 Methods)
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 doing 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 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.
