Custom Number Format

adrienne0914

Board Regular
Joined
Mar 22, 2018
Messages
54
I would like to create a custom number format where millions are displayed as thousands. I know the formula for that is #,##0,;-#,##0, My problem is that I also need to add an accounting offset dollar sign.

So I need $ 1,000,000 displays as $ 1,000 with the dollar sign in the same position no matter how long the number is. We've been creating custom number formats for each length, but would be great to have one formula. Is this possible?
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,848
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Does this do what you're looking for?
Code:
$ #.00,;-$ #.00,
 

adrienne0914

Board Regular
Joined
Mar 22, 2018
Messages
54
Does this do what you're looking for?
Code:
$ #.00,;-$ #.00,

Not exactly. The problem is that the dollar sign is in different locations depending on how long the number is:
$ 1996.49$ 1648.02
$ 1867.83$ 840.31

<tbody>
</tbody>

I need the dollar signs to line up.
 
Last edited:

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,848
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
How about this?
Code:
_($* #,##0.00,_);_($* (#,##0.00,);_($* "-"??_);_(@_)
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,848
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
 

Watch MrExcel Video

Forum statistics

Threads
1,109,466
Messages
5,528,972
Members
409,848
Latest member
Blomsten
Top