How to format xx,xx numbers as xx,xxx

[sillywilly]

Hi everyone:


I have a sheet that has a lot of data on it. Problem is, looks like a lot of the numbers got truncated somewhere down the line and so thousands values look like 31,45 instead of, say, 31,450.

To make matters worse, in each column, the majority of numbers are formatted as 31,45 but then SOME of them are formatted as 31450. That is, most cells just need a zero put in three spaces to the right of the comma, and OTHER cells actually need a comma.

For my purposes, I don't need a comma. But it's important to have because my numbers range from x,xxx - xxx,xxx. So without the comma, the program can't tell the different between 9,430 that's been written in 9430 format and 94,300 that's been written in 94,30 format.

As far as I know, there aren't any numbers written in 43,540 format—that is, non-truncated but with a comma. So I'm pretty sure than any value with no comma that looks like 46740 is meant to be 46,740 while any value with a comma has been truncated, so that if 46,740 were written in comma format it would appear as 46,74.

I hope this is enough information and has been explained correctly. Happy to field questions and try things out!
thanks!

[AlKey]

Try this formula
in B1 and copy down

=--IF(ISNUMBER(FIND(",",A1)),A1&"0.00",A1)

Row\Col	A	B
1	31,45	31450
2	31450	31450
3	43,54	43540

[sillywilly]

Hi-
Thanks for the reply.

For me, for some reason, that kept the numbers exactly the same. For example, I tried it on 31.25 and it spit back out 31.25 instead of 31.250 or something.

did I do something wrong?

thanks!

[AlKey]

31.25 is a valid number, there is nothing needs to be done with it. To get 31.250 you simply need to change the decimal number. However, it is difficult to tell whether some of these numbers are real numbers or text. I would suggest to upload a sample spreadsheet.