How do I remove text and switch place of comma and period in Google Sheets

user_4165734 0
Viewed 325

I'm trying to convert this text: $ 12,345.67 USD into 12.345,67. So, remove everything but digits and comma/period but reverse comma/period.

I currently have this:

=SUBSTITUTE(REGEXREPLACE(B222;"[^\d\.]+";);".";",")

This removes the text and the period, and then substitutes the comma with a period. The issue us that it leaves me with 12345,67 without the period and I'd like to see if I can get the right format for the number, even though it works as is.

google-sheets substitution regex
1 Answers

You can use

=REGEXREPLACE(REGEXREPLACE(B222;"^\D+|\D+$"; ""); ",(\d+)\."; ".$1,")

Details

  • REGEXREPLACE(...;"^\D+|\D+$"; "") will remove all non-digit chars at the start and end of the string, see demo
  • REGEXREPLACE(...;",(\d+)\."; ".$1,") will swap , and . around the number. See demo.
user_38329700
Related