Removing dollar signs in Google Sheets is a common task for users cleaning up imported data or standardizing financial formats. Often, currency symbols arrive embedded within cell text, preventing calculations and causing frustrating errors in formulas. This guide provides several reliable methods to strip these characters while preserving the underlying numerical values.
Understanding Why Dollar Signs Appear
Dollar signs usually appear when data is imported from external sources like CSV files, databases, or web scraping tools. These systems often store currency values as plain text, including the symbol for clarity. Google Sheets attempts to interpret this data but sometimes retains the formatting, leaving you with text that looks like currency but behaves like static text.
Method 1: Using the SUBSTITUTE Function
The SUBSTITUTE function is the most direct way to remove specific characters from text strings. It allows you to target the dollar sign and replace it with nothing, effectively deleting it from the cell content.
Step-by-Step Implementation
To implement this method, you create a new column adjacent to your current data. In the first cell of the new column, enter a formula that references the original cell and applies the SUBSTITUTE function. The structure of the formula is straightforward and easy to replicate down the entire column.
Select an empty cell next to the value you want to clean.
Type the formula: =SUBSTITUTE(A1, "$", "")
Press Enter and drag the fill handle down to apply the formula to other rows.
This formula specifically targets the dollar sign character and removes it without altering the remaining numbers or decimal points.
Method 2: Leveraging the TEXT Function for Conversion
While SUBSTITUTE cleans the display, you might need to convert the text back into actual numbers. The TEXT function, combined with VALUE, ensures that your cleaned data is recognized as numerical values suitable for calculations.
After removing the symbol, you might notice that the results are still left-aligned, indicating text format. To fix this, wrap your SUBSTITUTE formula inside a VALUE function. This conversion forces Google Sheets to interpret the string as a number rather than text.
Ensuring Numerical Integrity
Using VALUE ensures that sorting and arithmetic operations function correctly. Without this step, sorting numbers like 10 and 2 might result in 10 appearing before 2 because of lexicographical ordering.
Use =VALUE(SUBSTITUTE(A1, "$", "")) to convert text to a number.
Format the resulting column as "Currency" or "Number" via the toolbar.
Verify that the values are right-aligned, confirming they are numerical.
Method 3: Find and Replace for Static Data
For static datasets that you do not need to update dynamically, the Find and Replace tool is the fastest option. This method permanently alters the cell content, removing the symbols in a single operation.
Unlike formula-based methods, this approach modifies the original cell values. Use caution if you need to retain the original data with symbols for auditing or verification purposes.
Executing a Global Replace
To execute this method, highlight the range of cells you want to modify. Open the Find and Replace dialog box and enter the dollar sign in the "Find" field. Leave the "Replace with" field empty to delete the character.
Select the cells containing the dollar signs.
Press Ctrl + H or go to "Edit" > "Find and replace".
Enter $ in the search field and leave replace blank.
Click "Replace all" to remove all instances.
