Hello everyone. In this article, we will learn how to remove currency symbol in google sheets. We mostly work with money-related data, such as sales, profit, investment, and so many similar values we use which are money related, so most of the time we have the currency sign with these values, and for many reasons, we want to hide or remove them from specific positions. It can be a little annoying having a USD written after every value. Or it can be a dollar sign as well before or after the value. There are many cases where we either have an acronym of the currency such as USD, or the sign of the currency such as $ before or after the value. This can be annoying so we need to remove them because they have nothing to do with the calculation. So today we are going to learn several methods for doing this.
When to Remove Currency Symbols in Google Sheets
Most of the time have money-related data, and we can have various currencies which are not even known by other users or readers, so it can be confusing.
The other reason is to keep the data very simple and remove all the signs, symbols, or currency acronyms to keep the data clean, precise, and understandable.
Some people like to have these symbols, but nowadays mostly money-related data is represented in USD, and secondly EUR, and sometimes GBP. So, these are some top currencies of the world that are mostly used and shown in the data, it’s understandable that they are in these currencies, so we primarily don’t need to mention these currencies’ acronyms or symbols with each value. It makes the data more redundant and confusing for some readers. So, therefore, today we are going to learn how to remove currency symbol in google sheets.
How to Remove Currency Symbol in Google Sheets
We have so many methods to remove currency acronyms or symbols from our data. As there are cases where we have the symbol or acronym in the beginning or sometimes at the end. So, understanding this is important before using any method. So, let’s start with some sample data and learn how to remove currency symbol in google sheets.
Remove Currency Symbol in Google Sheets – Change Formatting to Number
In this section, we will learn how to remove currency symbols in google sheets by changing formatting to numbers. Here, I have some sample data below, where I have money-related values in USD ($), we will use this dataset to implement our methods and we will see how to remove this $ symbol.
Here, I have revenue amount in column B, and they all have leading $ symbol
Select all the values in column B
Go to Format in the main menu
Go to Number > then select Number
Doing this will convert the selected values into plain numbers, and the currency sign either $ or any other will be removed.
To decrease the decimal points, you can use a direct button in the toolbar below the main menu
Decrease decimal places: to decrease trailing decimal values by 1
Increase decimal places: to increase trailing decimal values by 1
This is how easily you can remove the currency symbol in google sheets using this method.
Remove Currency Symbol in Google Sheets – using Find and Replace
In this section, we will learn how to remove currency symbol in google sheets using the find and replace method. This is a very common method and can be used in various cases, here one thing to focus on is the type of symbol we have in our data.
There are two possibilities, either you have the currency symbol coming from the formatting, or you may have the symbol written by the keyboard. So, the find and replace method only work with the second method, if you try to remove the dollar symbol which is added by the format, so unfortunately it will not be removed using this method. So, make sure to use this method only to remove the currency symbols that are manually typed
See the below example to understand it properly
I have data in which I have two columns for revenue, in the first I have $ symbols that are coming from formatting, and in the second column, I have $ symbols that are typed manually.
Detect which $ symbols are coming formatting
If the $ sign is visible in the formula bar, then it’s typed manually
and if not showing in the formula bar, then it’s coming from the formatting and will not be removed by the find and replace method.
Open the find and replace dialog box (ctrl + h is the shortcut key)
Type the $ symbol in the Find field
Type a space (press the space bar key one time), in the replace field
Click on the replace all button below, and then click done.
Doing this will remove all the $ sign (or any currency sign) from the sheet, but this method will not work for the currency symbols that are coming as formatting.
The find and replace method will detect the $ even which is not typed manually, but it cannot remove it because it’s not a value, it’s the part formatting, and the find and replace method is designed to find and replace values, not the formatting.
Don’t worry we have another method that will help you remove currency symbols whether they are coming from formatting or manually typed.
Remove Currency Symbol in Google Sheets – using Custom Formula
In this section, we will learn how to remove currency symbol in google sheets using a custom formula. Now, this method is the solution to everything, both of the above methods can be replaced by this method. This method uses a combination of RIGHT and LEN functions to make logic, that removes the first character (which is a currency symbol) from the given value or cell reference.
For this method, I am using the same dataset, and I will show you how this method can remove both types of currency symbols, manually typed, and coming from formatting.
Type the formula in any cell
The above formula will remove the currency symbol from the first left side position
Press the Enter key, and you’re done.
Try the same formula to remove the typed currency symbols
It can also remove the typed ones. Great!!
This is how you can remove any kind of currency symbol and get the new value as a pure number.
What if I have the currency symbol at the end of the value?
Don’t worry, we need to tweak this formula a little bit to remove the currency symbol from the end.
Remove Currency Symbol in Google Sheets from the End Position
In this quick section, we will learn how to remove currency symbols in google sheets from the end of the value, for some reason a manually typed currency symbol can be at the end as well, so to remove those symbols from the end, we will use the same custom formula as we used above in the previous section. We need to change the formula a little bit to tell google sheets, that we want to remove the symbol from the end, and not from the beginning
Sample data with the currency symbols at the end
Type the formula we used above, and change the RIGHT function with LEFT
Note: Now the LEFT function will do the same just change its position, so now it will remove the symbol from the end
Press Enter key and you’re done
This is how you can remove the currency symbol from the end
Remove Currency Symbol in Google Sheets – Remove acronym (USD)
Sometimes, we have money-related data where the currency is shown by its acronym (USD), and not by its symbol ($), this is a manually typed currency acronym, so it can be removed by using the LEFT and RIGHT function along with LEN function in google sheets.
I have a data set in which I have some values having USD and other currencies written as prefixes to amount, and some values are there which have USD and other currencies written as postfix to amount. In simple words, we have some values like this “USD 1000”, and some like this “1000 EUR”. We will see how to remove both of these acronyms and get the pure values.
Use the RIGHT function to remove the currency acronym written as a prefix (left side of the value)
A problem with this function is that the numbers to return (6) are hard coded, and if any value has only 4 digits, then the rest will also be a return to complete the 6, so that’s why we use this function with length to find the length of the value and then remove the number of characters from it
So, we will use
Minus the LEN with three (as the USD are three characters, if any currency has less or more than 3 characters, you will minus the numbers accordingly)
Press Enter key, and you get the result
Use the LEFT function to remove the currency acronym written as postfix (right side of the value)
Minus the length of the characters in the currency acronym
Press Enter key and you get the result
This is how we can remove all the possible currency symbols and currency acronyms from the values using several methods in google sheets.
There are still some methods that can be used like REPLACE and MID function, but I think for this article, the methods we discussed are more than enough to remove currency symbol in google sheets.
Download/Copy Google Sheets Workbook
- The find and replace function cannot remove the currency symbol coming from the formatting because it’s not a number or text. It’s only a dummy value that is not visible when you get into the edit mode of the cell
- You can use any function like REPLACE, MID, SUBSTITUTE, LEFT, or RIGHT to remove the currency symbols from the values all these methods will return you a new value and will not change the original value.
- We have used $ USD currency in this tutorial because everyone identifies this currency mostly in the world, you can use any currency (all currencies are available in google sheets). All the above methods work accurately with any currency symbols or acronyms
This is all about how to remove currency symbol in google sheets. We have discussed every possible use case you can face when working with money-related data. This tutorial is meant to teach you how to remove currency symbols or their acronyms automatically by using formulas, methods, or functions. If you have one or two values with currency symbols, I will suggest you remove them manually. Methods covered in this tutorial save time and help you maintain large datasets with many columns and cells.
So that’s all from how to remove currency symbol in google sheets. I will see you soon with another helpful tutorial. Keep learning with Office Demy.