Extract Numbers Prefixed by Currency Signs in Google Sheets

In a string within a cell in Google Sheets, you may encounter numbers that are either with or without currency signs. Extracting only the numbers that are prefixed by currency signs can be a useful task. In this article, I will show you a simple Google Sheets formula to achieve this.

Why Use SUBSTITUTE and SPLIT to Extract Numbers Prefixed by Currency Signs?

To extract the currency-prefixed number, you can use the REGEXEXTRACT function with the regular expression $([0-9.]+). This formula will extract the first currency-prefixed number in the given string.

=REGEXEXTRACT(A2, "$([0-9.]+)")

However, this formula only returns the first occurrence of the currency-prefixed number. To extract all occurrences, you can combine the SUBSTITUTE and SPLIT functions with the REGEXEXTRACT function. Here’s how:

1. Use SUBSTITUTE to prefix a ‘~’ sign before the currency prefix.

   =SUBSTITUTE(A2,"$","~$")

2. Use SPLIT to split the string using the ‘~’ sign as the delimiter.

   =SPLIT(SUBSTITUTE(A2,"$","~$"),"~")

Now you can use the above combo as the cell reference in the earlier REGEXEXTRACT formula. Since there are multiple strings resulting from the split, wrap the REGEXEXTRACT function with ARRAYFORMULA.

=ArrayFormula(IFNA(REGEXEXTRACT(SPLIT(SUBSTITUTE(A2,"$","~$"),"~"), "$([0-9.]+)")*1))

This formula will return all the extracted currency-prefixed numbers from the string.

Sum Extracted Currency

If you want to extract currency-prefixed numbers and then sum them, you can use the SUM function along with the above formula. Here’s how:

=ArrayFormula(SUM(IFNA(REGEXEXTRACT(SPLIT(SUBSTITUTE(A2,"$","~$"),"~"), "$([0-9.]+)")*1)))

This formula will sum all the extracted currency-prefixed numbers and give you the total.

Please note that if your default currency format in Google Sheets is Dollar, you may encounter an issue with the REGEX function. To fix this, wrap the split output with the TO_TEXT function, which converts/formats numeric values to text values.

=ArrayFormula(SUM(IFNA(REGEXEXTRACT(TO_TEXT(SPLIT(SUBSTITUTE(A2,"$","~$"),"~")), "$([0-9.]+)")*1)))

And that’s it! You can now easily extract currency-prefixed numbers from a string and perform calculations on them in Google Sheets.

For more information and tips on working with numbers in Google Sheets, you can visit Crawlan.com.

Additional Resources:

• Extract All Numbers from Text and SUM It in Google Sheets
• How to Get Your Own Country’s Currency Format in Google Doc Spreadsheet
• Lookup Dates and Return Currency Rates in an Array in Google Sheets
• Format Numbers as Currency Using Formulas in Google Sheets
• How to Use the GoogleFinance Function in Google Sheets

Remember, with the power of Google Sheets, extracting and manipulating data becomes a breeze!