Hi to all of you. We will discover how to remove currency symbol in Google Sheets in this article. We deal mostly with financial data, including sales, profit, investments, and many other similar financial values. As a result, we frequently have the currency sign associated with these values, which we wish to hide or delete from particular positions for a variety of reasons.
Seeing USD written after each value can get a little tiresome. Alternatively, the dollar symbol may appear either before or after the figure. There are numerous situations where the currency sign, such as $, or its acronym, USD, appears before or after the value. Since they are unrelated to the computation and can be bothersome, we must delete them. We will therefore be learning multiple approaches to accomplish this today.
When we need to Remove Currency Symbols in Google Sheets
Formatting Data: It could be beneficial to remove currency symbols from financial data to unify the format and facilitate calculations and comparisons.
Operations with Numbers: To make sure that the data is in a numeric format before performing any numerical operations or computations on numbers, the currency symbols should be removed. Accurate mathematical procedures depend on this.
Data Imported: The currency symbols might be added if you import data into Google Sheets from outside sources like databases or CSV files. By deleting them, you can make sure that your current spreadsheet structure is consistent and compatible.
Making Charts: Numerical numbers without currency symbols improve the visual representation of data when making charts or graphs. It helps in providing a picture that is clear unambiguous and free of distracting symbols.
Conditional Layouting: Having constant numerical values devoid of currency symbols guarantees that formatting rules are correctly applied when conditional formatting is done based on numerical thresholds.
You may also like>>> How To Make A Scatter Plot In Google Sheets [Best Guide]
How to Remove Currency Symbol in Google Sheets
There are numerous ways to remove monetary acronyms or symbols from our information. In some cases, the symbol or acronym appears at the start, and occasionally at the end. It is essential to comprehend this before utilizing any technique. Now let’s learn how to remove currency symbol in Google Sheets using some sample data.
Remove Currency Symbol in Google Sheets – By Changing Formatting to Number
This section explains how to convert the formatting of Google Sheets to integers to remove the currency symbols. I’ve included some sample data below with money-related variables in US dollars ($). We’ll utilize this dataset to put our methods into practice and figure out how to get rid of the $ sign.
Step 1>
In column B, I have the income amount, and each one has a leading $ sign (Using Sample Data).
Step 2>
Select every value in column B.
Step 3>
Navigate to Format from the main menu.
Step 4>
Navigate to Number > and choose Number.
Step 5>
By doing this, the chosen values will be converted to simple numbers and the $ or other currency symbol will be removed.
Step 6>
There is a direct button on the toolbar beneath the main menu that you can use to reduce the decimal points.
Reduce decimal places: cut the values of the trailing decimal places by one.
To raise the trailing decimal values by 1, increase the decimal places.
This is how simple it is to use this method to remove the currency symbol from Google Sheets.
Remove Currency Symbol in Google Sheets – By Using Find and Replace
This section explains how to use the search and replace method to remove the currency sign from Google Sheets. While there are many situations in which this approach can be applied, one thing to keep in mind is the kind of symbol that is present in our data.
There are two possible results: either the currency symbol is entered by the keyboard, or it comes from the formatting. Consequently, the search and replace method only functions with the second approach; if you want to delete the dollar symbol that the format introduced, this method will sadly not be able to do it. Therefore, be careful to only use this procedure to delete the manually inserted currency symbols.
Take a look at the sample below to fully get it.
My data consists of two columns for revenue: formatting-generated $ symbols appear in the first column, while manually typed $ symbols appear in the second.
Step 1>
Demo Data
Step 2>
Determine which $ symbols are coming in formatting
When the $ symbol appears in the formula bar, it indicates that it was manually inputted.
Moreover, if it is not displayed in the formula bar, it originates from formatting and cannot be removed using the search and replace method.
Step 3>
Open the find and replace dialog box. The shortcut key to open the find and replace dialog box is ctrl + h.
Step 4>
In the Find area, type the $ sign.
Step 5>
In the replace field, type a space (press one space bar).
Step 6>
Click replace all buttons below, then hit “Done“.
By doing this, all $ signs and other currency signs will be removed from the sheet; however, the currency symbols that are coming as formatting will not be removed using this method.
Although the $ symbol is not manually typed, the find and replace technique can still identify it and replace it. This is because the $ symbol is part of the formatting and cannot be removed; its purpose is to find and change values alone.
Fear not—we have an alternative method that will assist you in eliminating currency symbols, whether they were typed by hand or came from formatting.
Remove Currency Symbol in Google Sheets – By Using Custom Formula
This section explains how to use a custom formula to remove the currency sign from Google Sheets. This approach is the panacea; it can take the place of the two ways mentioned before. This method removes the first character (a currency sign) from the given value or cell reference by using the RIGHT and LEN methods to create logic.
I will demonstrate how this solution, which makes use of the same dataset, may eliminate both manually written and formatting-derived currency symbols.
Step 1>
Demo Data
Step 2>
In any cell, type the formula.
=RIGHT(I2,LEN(I2)-1)
The currency symbol will be removed from the first left-side position using the procedure above.
Step 3>
Once you hit the Enter key, you’re finished.
Step 4>
Use the same procedure to remove the currency symbols you typed.
Step 5>
The typed ones can also be removed. Fantastic!
By doing this, you can obtain the new value as a pure number and eliminate any type of currency symbol.
What happens if the value ends with the currency symbol?
Don’t worry, we just need to make a few minor adjustments to this formula to get rid of the last currency sign.
Remove Currency Symbol in Google Sheets from the End Position of Value
This short section explains how to remove currency symbols from Google Sheets values that have been manually typed at the end. To do this, we’ll use the same custom formula that we used in the previous section to remove any symbols that have been manually typed at the end. To instruct Google Sheets that we wish to remove the sign at the end rather than the beginning, we must slightly alter the formula.
Step 1>
An example of data with the end of value currency symbols
Step 2>
Enter the previously used formula and use LEFT to modify the RIGHT function.
Note: The LEFT function will now do the same actions, but shift its location, so the symbol at the end will no longer be present.
Step 3>
Hit the Enter key to finish.
Here’s how to remove the end-of-value currency symbol:
Remove Currency Symbol in Google Sheets – Remove or Delete acronym (USD)
Occasionally, we come across financial data where the currency is displayed by its acronym (USD) rather than its symbol ($). Since this is a manually entered currency abbreviation, it can be removed by utilizing Google Sheets’ LEFT and RIGHT functions in conjunction with the LEN function.
I have a data collection with certain values where the amounts are written in USD and other currencies as prefixes and other values where the amounts are written in USD and other currencies as postfixes. To put it plainly, we have values like “USD 1000” and “1000 EUR“, respectively. We’ll look at how to obtain the pure values by removing both of these acronyms.
Step 1>
Demo Data
Step 2>
To remove the currency acronym written as a prefix (left side of the value), use the RIGHT function.
=RIGHT(S2,6)
We use this method with length to get the length of the value and then delete the number of characters from it since this function has a problem with the hard-coded numbers that it returns (6). If any value contains just four digits, the remaining two will also be returned to complete the six.
So, we are using
=RIGHT(S2,LEN(S2)-charcters)
Step 3>
Subtract the LEN by three (because the USD consists of three characters; if a currency contains fewer or more characters, the numbers will be subtracted appropriately).
Step 4>
When you hit the Enter key, the result appears.
Step 5>
To remove the currency acronym written as a postfix (right side of the value), use the LEFT function.
Step 6>
minus the number of characters in the acronym for the currency
Step 7>
Once you hit the Enter key, the result appears.
This is how using a few different techniques in Google Sheets, we can exclude every potential currency symbol and currency term from the data.
Although there are still more techniques, like as the MID function and REPLACE, I believe that the methods we covered in this article are more than sufficient to remove the currency sign from Google Sheets.
You may also like>>> How To Use Color Scale In Google Sheets [Comprehensive Guide]
Things to Remember
The currency symbol from the formatting cannot be removed using the search and replace function because it is neither a number nor a text. It’s merely a dummy value that is hidden when you enter the cell’s edit mode.
To remove the currency symbols from the values, you can use any function, such as REPLACE, MID, SUBSTITUTE, LEFT, or RIGHT. Each of these ways will return a new value; the original value will not be altered.
We have selected US dollars ($ USD) in this lesson since it is the most widely recognized currency worldwide; however, you are free to use any other currency (all currencies are available in Google Sheets). Any of the aforementioned techniques accurately operate with acronyms and financial symbols.
Frequently Asked Question
Are these methods applicable to group projects in Google Sheets?
Of course. The techniques covered are compatible with Google Sheets’ collaborative features. Whether you’re working alone or in a group, you can easily implement them.
Do the given methods work with different currencies?
Certainly. This tutorial’s techniques are flexible and may be used to remove currency symbols for a variety of currencies, making it appropriate for a range of financial situations.
Is it possible to manually exclude currency symbols for a limited set of values?
Yes, manual removal is a viable method for a restricted set of variables. Nonetheless, the techniques discussed in this lesson are made to handle bigger datasets quickly and effectively, saving you time and effort.
Conclusion
This lesson has given an in-depth description of how to remove currency symbols in Google Sheets. From investigating different use cases to providing automated solutions via functions and formulas, our goal has been to improve efficiency—particularly when working with large datasets. Although manual removal is effective for small datasets, our techniques handle larger datasets, saving you time. These methods enable you to manage financial data precisely, regardless of your role—finance expert or data analyst. Use these methods to make your spreadsheet work go more smoothly, and check back for additional instructions.
