Extracing numbers in different formats from cells in Google Sheets-CodePudding

I want to extract numbers from cells that contains text and numbers in Google Sheets. However, the format of the numbers is inconsistent. Thousands separator and decimal separator are not used consistently.I have some mock data below:

Column A in the amount of $1910.06 on una transferencia de 15,01 EUR a la received 650,40 € on in the amount of 1.009,62 EUR montant de € 16.19 vers in the amount of 99.77 PLN in the amount of CDN$ 1209.23

I am looking for the following output in column B: Column B 1910.06 15.01 650.04 1009.62 16.19 99.77 1209.23

I was trying to work with REGEXEXTRACT but don't know what the correct parameters are to make this work. Many thanks for your help.

CodePudding user response：

use:

=ARRAYFORMULA(REGEXEXTRACT(SUBSTITUTE(A1:A7&"", ",", "."), "\d (?:.\d )?")*1)

CodePudding user response：

Use this formula

=ArrayFormula(IF(A2:A="",,
 TRIM(REGEXREPLACE(REGEXREPLACE(A2:A,"[A-Za-z] ", ""), "[!@#$%€^&*()]", ""))))