number formatting/separators

S

Serene

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?
 
P

pshepard

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
 
S

Serene

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.
 
P

pshepard

Hi Serene,

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

Let me know if this works for you.

Thanks,
Peggy
 
S

Serene

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

pshepard said:
Hi Serene,

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

Let me know if this works for you.

Thanks,
Peggy
Click to expand...
 
S

Serene

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.
 
D

Dave Peterson

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.
 
Top