How to Remove Currency Symbol in Google Sheets [5 Methods]

how to Remove Currency Symbol in Google Sheets 14

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.

See also  How to Convert Formulas to Values in Google Sheets (Complete Guide)

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.

Step 1

Sample data

Here, I have revenue amount in column B, and they all have leading $ symbol

how to Remove Currency Symbol in Google Sheets 1

Step 2

Select all the values in column B

how to Remove Currency Symbol in Google Sheets 2

Step 3

Go to Format in the main menu

how to Remove Currency Symbol in Google Sheets 3

Step 4

Go to Number > then select Number

how to Remove Currency Symbol in Google Sheets 4

Step 5

Doing this will convert the selected values into plain numbers, and the currency sign either $ or any other will be removed.

how to Remove Currency Symbol in Google Sheets 5

Step 6

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

how to Remove Currency Symbol in Google Sheets 6

This is how easily you can remove the currency symbol in google sheets using this method.

See also  How to Sort by Number in Google Sheets (A-Z or Z-A)

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.

Step 1

Sample data

how to Remove Currency Symbol in Google Sheets 7

Step 2

Detect which $ symbols are coming formatting

how to Remove Currency Symbol in Google Sheets 8

If the $ sign is visible in the formula bar, then it’s typed manually

how to Remove Currency Symbol in Google Sheets 9

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.

Step 3

Open the find and replace dialog box (ctrl + h is the shortcut key)

how to Remove Currency Symbol in Google Sheets 10

Step 4

Type the $ symbol in the Find field

how to Remove Currency Symbol in Google Sheets 11

Step 5

Type a space (press the space bar key one time), in the replace field

how to Remove Currency Symbol in Google Sheets 12

Step 6

Click on the replace all button below, and then click done.

how to Remove Currency Symbol in Google Sheets 13

how to Remove Currency Symbol in Google Sheets 14

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.

See also  How to Use Conditional Formatting in Google Sheets (Complete Guide)

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.

Step 1

Sample data

how to Remove Currency Symbol in Google Sheets 15

Step 2

Type the formula in any cell

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

how to Remove Currency Symbol in Google Sheets 16

The above formula will remove the currency symbol from the first left side position

Step 3

Press the Enter key, and you’re done.

how to Remove Currency Symbol in Google Sheets 17

how to Remove Currency Symbol in Google Sheets 18

Step 4

Try the same formula to remove the typed currency symbols

how to Remove Currency Symbol in Google Sheets 19

Step 5

It can also remove the typed ones. Great!!

how to Remove Currency Symbol in Google Sheets 20

how to Remove Currency Symbol in Google Sheets 21

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.

See also  How to Make Organizational Chart Template in Google Sheets

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

Step 1

Sample data with the currency symbols at the end

how to Remove Currency Symbol in Google Sheets 22

Step 2

Type the formula we used above, and change the RIGHT function with LEFT

how to Remove Currency Symbol in Google Sheets 23

Note: Now the LEFT function will do the same just change its position, so now it will remove the symbol from the end

Step 3

Press Enter key and you’re done

how to Remove Currency Symbol in Google Sheets 24

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.

Step 1

Sample data

how to Remove Currency Symbol in Google Sheets 25

Step 2

Use the RIGHT function to remove the currency acronym written as a prefix (left side of the value)

=RIGHT(S2,6)
how to Remove Currency Symbol in Google Sheets 26

how to Remove Currency Symbol in Google Sheets 27

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

=RIGHT(S2,LEN(S2)-charcters)

Step 3

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)

how to Remove Currency Symbol in Google Sheets 28

Step 4

Press Enter key, and you get the result

how to Remove Currency Symbol in Google Sheets 29

how to Remove Currency Symbol in Google Sheets 30

Step 5

Use the LEFT function to remove the currency acronym written as postfix (right side of the value)

how to Remove Currency Symbol in Google Sheets 31

Step 6

Minus the length of the characters in the currency acronym

how to Remove Currency Symbol in Google Sheets 32

Step 7

Press Enter key and you get the result

how to Remove Currency Symbol in Google Sheets 33

how to Remove Currency Symbol in Google Sheets 34

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

Important Notes

  • 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

See also  How to Make a Bar Graph in Google Sheets (Quick Guide)

Conclusion

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

We will be happy to hear your thoughts

Leave a reply

OfficeDemy.com
Logo
Enable registration in settings - general