In Excel 2007, how do I get the minus sign entered immediately before the number?

Try custom format and set -0 in the Type filed if all numbers are integer.

I'm not sure it will work because I have already this customized format set: ### ### ##0 kr

I've checked it.
 
please check this test file.
Test.xlsx

I can't get it to work, maybe because I use blank space as thousand separator. Is it any way to combine both?: thousand separator and minus sign immediately before the number?

For example, I can't enter the minus sign anywhere in my formula for thousand separator:

### ### ##0 kr

Not possible:
### ### ##-0 kr
### ### -##0 kr
-### ### ##0 kr

Well, I can't have the comma as thousand separator, but it must be a blank space as thousand separator. So it's like the minus doesn't fit in anywhere in a customized format here.

Hi,

Try this as a custom format:

### ### ##0 k\r;-### ### ##0 k\r;0 k\r

If you want to also include the default text format (@) then:

### ### ##0 k\r;-### ### ##0 k\r;0 k\r;@


Alan.

Alan, I tried both your suggestions now, but I still get the same result: a blank space between the minus sign and the following number.

Hi,

Not ideal, but as a workaround, you could use multiple custom formats (I'd suggest setting them up as styles, but that's up to you):

For values up to 1,000:                               #0 k\r;-#0 k\r;0 k\r
For values up to 1,000,000:                       # ##0 k\r;-# ##0 k\r;0 k\r
For values up to 1,000,000,000:               # ### ##0 k\r;-# ### ##0 k\r;0 k\r

and so on, but if you have more than 1,000,000,000,000 Krona, then I would like to come work for you!


Alan.

Alan,

Your solution worked fine.

I changed a little because it should always be a minus sign before the value as it denotes a wager I've done:

-### ##0 k\r;0 k\r

But for I3 (which is the total of all wagers I've done throughout the years) this does not work:

-### ### ### ##0 k\r;0 k\r

Here I get a blank space after the minus sign for some reason.

Hi,

I think that would be:

# ### ### ##0 k\r;-# ### ### ##0 k\r;0 k\r

What is the value that is not working?

If it is, say, 1,234,567,890 then the above should work.

If it is, say, 1,234,567 then try this:

# ### ##0 k\r;-# ### ##0 k\r;0 k\r



Alan.

I tried your suggested modifications now Alan, but for both I get no minus sign at all displayed for the value "5 000". I tried changing to # ### ### ##0 k\r;-# ### ### ##0 k\r;0 k\r first, and then to # ### ##0 k\r;-# ### ##0 k\r;0 k\r.

But for both I get no minus sign, only "5 000" is displayed.

Solution provided