number formatting/separators
 

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

number formatting/separators
 

Hi Serene,

The following just displays the value from A1 with a hyphen in place of the
decimal.

="$"&TEXT(A1,"0")&"-"&TEXT(A1-INT(A1),"00")

Let me know if this helps.

Thanks,
Peggy

"Serene" wrote:

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

number formatting/separators
 

Hi psherpard
Thanks. I tried your formula but did not get the result I wanted. Result
should be $1,234-56 but I got $1235-01 using your formula. Pls help.

"pshepard" wrote:

Hi Serene,

The following just displays the value from A1 with a hyphen in place of the
decimal.

="$"&TEXT(A1,"0")&"-"&TEXT(A1-INT(A1),"00")

Let me know if this helps.

Thanks,
Peggy

"Serene" wrote:

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

number formatting/separators
 

Hi Serene,

="$"&TEXT(A1,"0,0")&"-"&TEXT(A1-INT(A1),"00")

Thanks,
Peggy

"Serene" wrote:

Hi psherpard
Thanks. I tried your formula but did not get the result I wanted. Result
should be $1,234-56 but I got $1235-01 using your formula. Pls help.

"pshepard" wrote:

Hi Serene,

The following just displays the value from A1 with a hyphen in place of the
decimal.

="$"&TEXT(A1,"0")&"-"&TEXT(A1-INT(A1),"00")

Let me know if this helps.

Thanks,
Peggy

"Serene" wrote:

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

number formatting/separators
 

Hi
The result is not correct $1,235-01.

"pshepard" wrote:

Hi Serene,

="$"&TEXT(A1,"0,0")&"-"&TEXT(A1-INT(A1),"00")

Thanks,
Peggy

"Serene" wrote:

Hi psherpard
Thanks. I tried your formula but did not get the result I wanted. Result
should be $1,234-56 but I got $1235-01 using your formula. Pls help.

"pshepard" wrote:

Hi Serene,

The following just displays the value from A1 with a hyphen in place of the
decimal.

="$"&TEXT(A1,"0")&"-"&TEXT(A1-INT(A1),"00")

Let me know if this helps.

Thanks,
Peggy

"Serene" wrote:

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

number formatting/separators
 

Hi Serene,

="$"&TEXT(A1,"0,0")&"-"&TEXT((A1-INT(A1))*100,"00")

Let me know if this works for you.

Thanks,
Peggy

"Serene" wrote:

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

number formatting/separators
 

How about:

=SUBSTITUTE(TEXT(A1,"$#,##0.00"),".","-")



Serene wrote:

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

--

Dave Peterson

number formatting/separators
 

Hi
still not right. The result is $1,235-56

"pshepard" wrote:

Hi Serene,

="$"&TEXT(A1,"0,0")&"-"&TEXT((A1-INT(A1))*100,"00")

Let me know if this works for you.

Thanks,
Peggy

"Serene" wrote:

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

number formatting/separators
 

Hi Dave
Wonderful. It works. However I realise that I cannot use this value to
compute. It will have a #value! error. How to convert this into a number ?
Thank you very much.

"Serene" wrote:

Hi
The result is not correct $1,235-01.

"pshepard" wrote:

Hi Serene,

="$"&TEXT(A1,"0,0")&"-"&TEXT(A1-INT(A1),"00")

Thanks,
Peggy

"Serene" wrote:

Hi psherpard
Thanks. I tried your formula but did not get the result I wanted. Result
should be $1,234-56 but I got $1235-01 using your formula. Pls help.

"pshepard" wrote:

Hi Serene,

The following just displays the value from A1 with a hyphen in place of the
decimal.

="$"&TEXT(A1,"0")&"-"&TEXT(A1-INT(A1),"00")

Let me know if this helps.

Thanks,
Peggy

"Serene" wrote:

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

number formatting/separators
 

Don't use that cell for further calculations--use the original cell.

If you really want (I wouldn't!!), you could convert it back:

=--SUBSTITUTE(B1,"-",".")

Be aware that a number like:

1234.5678
will lose some accuracy in the conversions.



Serene wrote:

Hi Dave
Wonderful. It works. However I realise that I cannot use this value to
compute. It will have a #value! error. How to convert this into a number ?
Thank you very much.

"Serene" wrote:

Hi
The result is not correct $1,235-01.

"pshepard" wrote:

Hi Serene,

="$"&TEXT(A1,"0,0")&"-"&TEXT(A1-INT(A1),"00")

Thanks,
Peggy

"Serene" wrote:

Hi psherpard
Thanks. I tried your formula but did not get the result I wanted. Result
should be $1,234-56 but I got $1235-01 using your formula. Pls help.

"pshepard" wrote:

Hi Serene,

The following just displays the value from A1 with a hyphen in place of the
decimal.

="$"&TEXT(A1,"0")&"-"&TEXT(A1-INT(A1),"00")

Let me know if this helps.

Thanks,
Peggy

"Serene" wrote:

I would like to have 2 decimal separator formats within a worksheet. For
example,
for $1,234.56 the format would be $1,234-56
for 7.89% the format would be 7.89%

How can I do that?

--

Dave Peterson