Forced Cell View with Thousands Separator

mst3kr

New Member
Joined
Apr 15, 2013
Messages
46
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
Hello all!

Straightforward question which I'm hoping has an easy answer.

I have a spreadsheet I manually fill out each day that captures manufacturing performance. The values are always whole numbers; ie, 51, 253, or 1,344, but when keying the values, I have to add the trailing 3 zeros to each number due to how the values need to be viewed by my leadership. So, the values will look like xx,xxx, xxx,xxx, or x,xxx,xxx. To minimize keystrokes and typos, I'd like to format the cell so that if I key just the whole number, the cell will show it with the trailing zeros and the hundreds & thousands separator. While I can get the cell format to include the trailing zeroes, for the life of me I cannot get a comma to separate the hundreds/thousands. I've tried numerous variations myself and surfed the web for solutions, but have come up empty. Without doing helper cells or columns, is it possible to accomplish this "in cell" with formatting?

Thanks!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I am not clear what you mean by trailing zeroes. Are you saying that you are entering 51 but you want it to be displayed as 51,000? (If you want 51 to look like 51,000 that's fine but it will still be 51 if you do arithmetic with it.)

If my description above is what you need then use this custom format:

_(* #,##0",000"_)

If not then please give concrete examples what you key in and what you want it to look like in the cell?
 
Upvote 0
Solution
I am not clear what you mean by trailing zeroes. Are you saying that you are entering 51 but you want it to be displayed as 51,000? (If you want 51 to look like 51,000 that's fine but it will still be 51 if you do arithmetic with it.)

If my description above is what you need then use this custom format:

_(* #,##0",000"_)

If not then please give concrete examples what you key in and what you want it to look like in the cell?
Hi Jazzer.

Thanks for the reply. Sorry for the lateness; I didn't get a notification someone had answered my question...

This is exactly what I was needing! Thank you, very much!
 
Upvote 0
I didn't get a notification someone had answered my question...
It looks like you are set up to get email notifications. You may want to check a few things:
1. The email address on the account is the current one you are using (since you first set it up in 2013, is it possible that you still have an old email address linked to the account)?
2. You may want to check your junk/spam folder (the emails often end up in those folders).
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top