CODEWITHSUNDEEP
google-sheets
Tom
Tom

Reputation: 233

Extracting numbers with REGEXEXTRACT that might have a comma or dot

I have a list of numbers in a few formats that may or may not include a dot and a comma. The numbers are locked in a string. For example:

hello 1,000 goodbye

hola 2,000.12 ciao

Hallo 3000.00 Auf Wiedersehen

How can I extract the numbers? I don't care if the comma is added but the dot is obviously important. I need the regular_expression to be used in REGEXEXTRACT (and the rest of the REGEX formulas. The output should be:

1000

2000.12

3000.00

Upvotes: 1

Views: 1792

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9345

Supposing that your raw data is in A2:A, use this in B2 (or the second cell) of an otherwise empty column:

=ArrayFormula(IF(A2:A="",,IFERROR(VALUE(REGEXEXTRACT(A2:A,"\d[\d,\.]*\d")))))

The REGEX portion reads, in plain English, "Extract any portion that starts with a digit followed by any number of digits, commas or periods (or none of these) and ends with a digit."

You will likely want to apply Format > Number > Currency to the results column.

Upvotes: 2

Related Questions