Can't work out special number format

[Frank H]

I posted this question a couple of days ago & got no
bites, hope you don't mind if I try again

I wish to format a whole number so that there is a dash
(minus sign) every third digit from the right.

eg 123 formats as 123
1234 as 1-234
12345 as 12-345
123456 as 123-456
1234567 as 1-234-567 and so on

I have tried combinations of '#' & '0' & '-' characters
but cannot get it to work

TIA
Frank

 

[Dirk Goldgar]

I posted this question a couple of days ago & got no
bites, hope you don't mind if I try again

I wish to format a whole number so that there is a dash
(minus sign) every third digit from the right.

eg 123 formats as 123
1234 as 1-234
12345 as 12-345
123456 as 123-456
1234567 as 1-234-567 and so on

I have tried combinations of '#' & '0' & '-' characters
but cannot get it to work

I don't see a way to do it with a format string alone, but you could do
it by combining the Format function and the Replace function:

=Replace(Format([NumberField], "#,##0"),",","-")

The above assumes that the comma is the "thousands separator", so
locales that use other separators-- the period, for example -- would
have to change the "," argument to the Replace() function.

?Replace(Format(123, "#,##0"),",","-")
123
?Replace(Format(1234, "#,##0"),",","-")
1-234
?Replace(Format(12345, "#,##0"),",","-")
12-345
?Replace(Format(123456, "#,##0"),",","-")
123-456
?Replace(Format(1234567, "#,##0"),",","-")
1-234-567