Google-sheets – Find and replace Google Script

google sheetsgoogle-apps-script

I've read a lot of questions and I tried several Script for Google Sheets but nothing works for a very simple case:

I would love to replace all the points "." with the commas "," in a specific range (E2:E999) in the same way I could do it with a Ctrl+H command, as you can see below.

enter image description here

Thank you very much for your help,
Marco

Best Answer

Here is a workaround, but in means two things:

  1. you will need to insert a new column (but you can hide the one that has the decimal points)

  2. It will convert the numbers into strings so you will not be able to use them for calculations (but they will look right). You will still be able to do any calculations you need using the original data (in the 'hidden' column)

Assuming that the number you want to reformat it in cell A1, then in another cell (e.g. B1), insert the following formula:

=text(A1,"00")&","&right(text(A1,"00.00"),2)

An example of the formula in action

You can also copy and "Paste values only" (Ctrl+Shift+V) so that you can then remove the original column (if you don't need it for calculations)

can paste as values only