Following problem. I have decimal numbers in my Google Sheets that I get from a website.
The numbers have 3 digits after the decimal point or they have no comma in there. I need the numbers without a comma and adding Zero after 1,2 => 1200 or 1,22 => 1220 and 2 should be 2.
Say if a number looks like this on the website:
1,200
1,220
1,222
12,220
2
I get the data via Google spreadsheet with the formula =IMPORTDATA("API_LINK")
There is the problem that the zeros that I don't want to lose are automatically deleted by =IMPORTDATA
Means I get that copied into the table like this :
1,2
1,22
1,222
12,22
2
My question is, how do I get the numbers to be automatically converted with another formula or even with a script that looks like this:
1,2 => 1200
1,22 => 1220
1,222 => 1222
12,22 => 12220
2 => 2
So normal numbers should be normal and decimals should be converted.
The formula what I tried but is only working for 1,222 => 1222. The other looke like this 1,2 => 43862 and 1,22 => 1,22 also 12,22 => 12,22.
=IFERROR(VALUE(SUBSTITUTE(A2;",";"."));VALUE(SUBSTITUTE(A2;".";",")))
I tried even to change the settings in the sheet but isn't working at all. It would be very nice if someone could help me with that.
If your import is removing final zeros, this indicates that what winds up in your sheet is most likely text and not a number at all.
It is always easier to help if you share a sample sheet with full editing permissions. However, given what I know from your post, try this:
=IF(A2="","",VALUE(A2&IFERROR(REPT("0",3-(LEN(A2)-FIND(",",A2))))))
This would also work as an array formula with slight modification (i.e., it could "solve" a whole column with one formula rather than dragging a formula down). For instance, if your raw data were in A2:A, you could put the following array formula in B2 (assuming column B is completely blank below B2):
=ArrayFormula(IF(A2:A="","",VALUE(A2:A&IFERROR(REPT("0",3-(LEN(A2:A)-FIND(",",A2:A)))))))