Thousands separator problem

D

davester

New Member
Joined
May 1, 2008
Messages
4
On my indows XP box, when I type a number into an Excel spreadsheet and it is in "General" format, the numbers appears like this (examples):

1234567

or this:

7.125

or this:

8.1

I want them to look just like that except for the first one, which I want to have the thousands separator:

1,234,567

I don't want to use a number format because
Excel makes you set a specific number of decimal places...I want the decimal places to float depending on how many decimal places there are in the entered number (just like the "General" format does). So basically, I want a "General" format, but with thousands separators (commas).

I tried using something like ###,###.### but that leaves a decimal place at the end of each integer (i.e. "123,456.") which is also not acceptable.

Does anybody know 1) what I am talking about, 2) how to fix it.

Thanks for any help.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
I believe something like this custom format would work:

###,###,###.######

You can have up to 15 digits - however you want to divide them up on either side of the decimal point.
 
Upvote 0
Or maybe:

###,###,##0.######

To at least show a zero when you have zero in the cell.
 
Upvote 0
Maybe no good...I just noticed the number without decimals values still show a decimal point:
3.
3.1419

Stumped. Seems like overkill to have event code on the entire sheet. Maybe some kind of CF Formula using Mod?
 
Upvote 0
Alexander Barnes:
I can't speak for XL2007, but Conditional Formatting doesn't have number formats.

davester:
This can be done with VBA, and an appropriate number format can be applied to the cell. Is there a specific range on the sheet you want this to happen on?
 
Upvote 0
Yes, no number formats there...darn!
 
Upvote 0
Yes, there are specific ranges that I would like to do this for, although it would be best if any such solution would ignore text cells, since some of the ranges are separated by text rows and columns. That way it would take less time to apply it (I'm talking about very large workbooks with many very large worksheets.

I'm a little surprised that the solution doesn't seem readily available, since many technical tables are formatted this way, with the number of decimal places in each cell based on the number of significant digits (we're looking at analytical lab data that is generally reported to a certain number of SDs). I also looked in conditional formatting first...sure would be nice if they added a few more format options.

BTW, I have never used VBA with Excel so if that is the only solution, rudimentary instructions would be appreciated.
 
Upvote 0
Does this need to deal with pre-existing data? If so, you may need a macro to deal with that. Are these all typed in values, as you indicated, or the results of formulas?

But after that, it would be code to change the values as you enter them.

What ranges and on what sheets?
 
Upvote 0
Hi davester:

Welcome to MrExcel Board!

Following is an illustration using TEXT function ...
Book2
ABCD
1
2123456123,456
32.492.49
41256.321,256.32
5
Sheet2


Depending on your project needs, constraints, and personal preferences, this proposed solution may or may not work for you.
 
Upvote 0
Yes, most of the data are preexisting data transmitted as electronic deliverables by laboratories (either as database files or comma-delimited data. Each workbook has about twenty or more tabs, with each tab being structured somewhat differently depending on the types of data.

Typically each sheet has a few header rows (sample location, date, etc)
and perhaps left hand rows (date, sample depth, etc, etc). There are sometimes columns that show the units used for each row, etc.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
macro for formatting numbers stored as text
Replies
3
Views
792
Joe4
Joe4
D
  • Question
Format number with thousands separator and all decimal
2
Replies
15
Views
847
dk2
D
B
  • Solved
Display result of formula which is millions as nn.n without losing original value of cell and no 'M' in the result cell either.
Replies
3
Views
350
awoohaw
awoohaw
C
  • Solved
Formatting a Cell Negative
Replies
2
Views
672
Cricketer
C
R
  • Question
Help with thousand and decimal separators
Replies
2
Views
577
Rockinfreakapottamus
R

Forum statistics

Threads
1,215,009
Messages
6,122,674
Members
449,091
Latest member
peppernaut

Share this page

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