How to Convert Currency Text to Number in Google Sheets

0
157
Convert Currency Text to Number in Google Sheets

Let’s learn here the tips to convert currency text to number in Google Sheets. When you import data from web pages in to 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. 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 contain 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 text.

example to convert currency text in Google Doc

You can convert that both the columns back to 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.

How this formula converts Currency in text 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.

formula result showing currency text to number in google sheets

Hope this makes things clear. That’s all. Enjoy.

LEAVE A REPLY

Please enter your comment!
Please enter your name here