3

I need to use custom number formatting or something similar that will create real blank spaces, NOT just make it simply appear differently in the Excel cells. This is because I am actually using a library that implements Excel functionality called SpreadsheetGear and my ultimate output is a string.

This is what I want:

When a number is 6 digits or less, I would like it to have formats like this:

$  xxx,xxx
$   xx,xxx
$    x,xxx
$      xxx
$       xx
$        x

As shown above, there will be 2 blank spaces between the "$" and the first digit for a 6 digit number, 3 blank spaces between the "$" and the first digit for a 5 digit number, etc.

In other words, the length of the characters of the number will always be the same for number with digits 6 or less.

For numbers with more than 6 digits, just normal number formatting with commas.

5
  • 1
    Try changing the cells to the Accounting Format
    – user304064
    Mar 24, 2014 at 18:54
  • @bobSmith1432 Unfortunately my situation is more complicated than that. I need to use custom number formatting or something equivalent. Mar 24, 2014 at 19:29
  • Put the $ sign in preceding column and right align both columns. If you print it, it would appear to be a single column as long as you don't specify border. Mar 24, 2014 at 23:01
  • If you are using Google Sheets, there is a custom formatting syntax that converts leading zero to space: ? A digit in the number. An insignificant 0 will appear as a space in the results. For example, '??0.00% would show ' 4.50% (two spaces before 4), ' 45.00% and '100.00% (ignore the quote ' mark).
    – ADTC
    Sep 14, 2017 at 6:52

6 Answers 6

0

If you don't mind storing the formatted version in a separate column and as string instead of number, you could use (assuming the value is in cell A1):

="$"&REPT(" ", 9-LEN(TEXT(A1,"#,##0")))&TEXT(A1,"#,##0")

Results formatted like this will look best with a fixed width font.

2

You could use conditional formatting as techie007 linked in conjunction with custom formatting.

For instance, select the range of cells and pick the option 'Highlight Cell Rules' > 'Less than...'

Put 10000000 (the highest 6 digit number is 999,999) for the condition and for the formatting, put 'Custom'.

In the dialog that pops up, go to the number formatting, Custom formatting and use $* #,##0.

2
  • It turns out that this does not work for my application, even though it displays somewhat correctly in Excel. The use of the asterisk function depends on the field and does not use real spaces. I need the number or string to have real blank spaces. Mar 24, 2014 at 21:40
  • @user1744318 Ahh well. So you don't really need the value in Excel? The end goal is actually to get those spaces into another application?
    – Jerry
    Mar 25, 2014 at 4:57
1

What you are looking for is the "repeat characters syntax".
You put an asterisk (*) and a space between the dollar sign ($) and the number format to get the dollar to the left and the number to the right (space is the character being repeated).

"$* 0" or "$* #,##0"

This also works fine for formatting dates. Month left and date right would be:

"mmmm* d"
1

The custom-formatting special character you're looking for is the question mark (?). It will display a digit if the number is long enough, a space if the number is not.

For six digits, with comma, and a leading dollar sign followed by two spaces per your sample the custom-format syntax is

$  ???,??0

The 0 ending is required as the ? format will replace leading or trailing zeros with spaces. This formatting will also round to the nearest whole number.

My reference: https://www.exceltactics.com/definitive-guide-custom-number-formats-excel/#Question-Mark

0

Create 6 conditional formats that look like this:

Conditional Formatting Dialog

The last conditional format is not visible in the screenshot, just add a zero.

Set their custom number formats to the following, follow this order from top to bottom:

$_0_0_0_0_0_0_0_,#
$_0_0_0_0_0_0_,#
$_0_0_0_0_0_,#
$_0_0_0_0#,###
$_0_0_0#,###
$_0_0#,###

The first three also allocate space for the missing comma.

The final result:

Excel Results

1
  • I guess I always knew that making six different formattings would work. I had hoped there was a simpler way. Mar 24, 2014 at 22:24
-1

Open Format cells → Numbers → Accounting

Specify:

  • Symbol $
  • Decimal places 0
1
  • Could you develop your answer? I have tested it and, while it may provide the same formatting, it doesn't do exactly what the Original Poster wants. May 1, 2014 at 17:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .