CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formularegexp-replace
gciriani
gciriani

Reputation: 679

Google Sheets Converting numbers imported from a different country, switching commas with periods

I'm importing numbers represented with commas instead of dots, and dots instead of commas, in Google sheets, and I need to convert them: from

32.072,25 to 32072.25
-1,06     to -1.06

For now I'm doing it with two nested RegExReplace expressions

REGEXREPLACE( REGEXREPLACE( B2, "\.","" ), 
              ",", "\." )

I believe that it could be done without nesting, as part of the same RegEx operation, but I'm not knowledgeable enough about the RegEx syntax and logic to find a way. Is it possible at all?

Upvotes: 0

Views: 56

Answers (1)

player0
player0

Reputation: 1

try:

=IF(REGEXMATCH(""&B2, "[.]"), 
 SUBSTITUTE(SUBSTITUTE(B2, ".", ), ",", "."), SUBSTITUTE(B2, ",", "."))

to get numeric values:

=1*IF(REGEXMATCH(""&B2, "[.]"), 
 SUBSTITUTE(SUBSTITUTE(B2, ".", ), ",", "."), SUBSTITUTE(B2, ",", "."))

Upvotes: 1

Related Questions