1

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.

  • 1
    Try changing the cells to the Accounting Format – user304064 Mar 24 '14 at 18:54
  • @bobSmith1432 Unfortunately my situation is more complicated than that. I need to use custom number formatting or something equivalent. – user1744318 Mar 24 '14 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. – tumchaaditya Mar 24 '14 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 '17 at 6:52
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.

  • 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. – user1744318 Mar 24 '14 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 '14 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"
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

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

Open Format cells → Numbers → Accounting

Specify:

  • Symbol $
  • Decimal places 0
  • 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. – Doktoro Reichard May 1 '14 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.