# Changing decimals to commas and missing zeros

#### Lou

##### New Member
I have a spreadsheet which has numbers with decimal thousand seperators rather than commas e.g. 178.455.136. If I do a find and replace I get 178,455,136 which is fine.

However there are some numbers e.g. 83.58 which needs to change to 83,580. I can change the number of decimal places of the original to 83.580 but when I do a find and replace it goes back to 83,58. There are also some numbers e.g. 972 which should be 972,000 or 59.3 which should be 59,300. I guess what I probably want is a way to force the original data to have the correct number of zeros at the end.

Hopefully there is a simple solution to this as i don't want to have to go through and add zeros manually.

Thanks for any help,

Lou

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Weaver

##### Well-known Member
If you go into tools -> options and select the 'international' tab, you can decide which separators to use where. If you open your spreadsheet after you've set these, does it display the correct number of zeros?

#### Andrew Poulsom

##### MrExcel MVP
Sorry, but if the dot is the thousands separator (rather than the comma) how can 83.58 be 83,580 and 972 be 972,000 and 59.3 be 59,300? There must always be 3 digits between thousands separators or between the thousands separator and the decimal separator.

If your data is that inconsistent there won't be a simple solution.

Replies
11
Views
6K
Replies
1
Views
624
Replies
6
Views
237
Replies
1
Views
458
Replies
5
Views
505

1,190,798
Messages
5,982,974
Members
439,810
Latest member
phobo3s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back