Let’s learn here the tips to convert currency text to number in Google Sheets.

When you import data from web pages into Google Sheets, most of the time, the column that containing currency may be in text format.

There may by $ symbol or any other currency symbol prefixed to it.

Earlier, I’ve shared a formula that can be used to extract numbers from text and then Sum it.

This’s in line with that tutorial. But here I am going to convert an entire column containing currency in text format to number.

Further, here there are 7 columns in the imported data in which two columns are containing currency but as text.

The formula that I am going to use here would return all the columns. But only the column that contains the currency text will be modified. So let’s begin with an example.

Convert Currency Text to Number in Google Sheets

In the below table, as you can see, column C and F have currency values but in the text format.

You can convert that both the columns back to the number, and populate the entire table in another location on the same sheet or another tab.

First, see the formula below. Then the explanation follows.

=query({A1:B12,ArrayFormula(REGEXREPLACE(C1:C12, “[^\d\.]+”,)*1),D1:E12,ArrayFormula(REGEXREPLACE(F1:F12, “[^\d\.]+”,)*1),G1:G12},”Select *”)

Note: copy and paste may not work.

Note: Copied formulas from this page would return errors. To avoid that once copy-pasted, re-type all the double quotes. 

How this formula converts Currency in text format to number in Google Sheets?

The highlighted two ArrayFormulas do this conversion. Both are same but the data ranges (column numbers) are different.

ArrayFormula(REGEXREPLACE(C1:C12, “[^\d\.]+”,)*1)

This ArrayFormula converts all the text in column C to numbers with the help of REGEXREPLACE function.

ArrayFormula(REGEXREPLACE(F1:F12, “[^\d\.]+”,)*1)

This function do the same conversion for Column F.

Now we want to join other columns with these converted columns. The Query formula does this part.

More Details

The use of Query in the above master formula to Convert Currency Text to Number in Google Sheets

This below Query formula can populate the first data as it is in a new location.

=query(A1:G12,“Select *”)

But we want to convert and incorporate two columns that contain currency text to numbers. So we have to modify this Query.

We should join columns with the help of Curly Brackets instead of using the range A1:G12 as above. So that we can incorporate the converted two columns with this range.

We should replace the below-highlighted range with the two ArrayFormulas we already have.

=query({A1:B12,C1:C12,D1:E12,F1:F12,G1:G12},”Select *”)

See the result.

I hope this makes things clearer to you. That’s all. Enjoy.