Show the decimal point as( :) instead of (.)

[rmtaylor]

Hi
I have been trying without success to make a sheet I have been working on show the decimal point as ) instead of (.) The best result I have managed is (.: )by entering (0. ":" 00) in the custom box in number formatting. I would be grateful if anyone can help as my efforts does not look very professional.
This message was edited by rmtaylor on 2002-04-21 01:07

 

[Chris Davison]

On 2002-04-21 08:39, smozgur wrote:
or to show A3 with ) again;

A3 => =SUBSTITUTE(SUBSTITUTE(A2,":",".")*5,".",":")

hmmmm... this flips it back to text

(check with =isnumber)

 

[Jack in the UK]

Chris

did you not check what i posted, i have no excel to test on quite yet but should work and will do the required results im sure. So oh! ok i side stepped Exel this once,,

 

[Chris Davison]

On 2002-04-21 11:49, Jack in the UK wrote:
Chris

did you not check what i posted, i have no excel to test on quite yet but should work and will do the required results im sure. So oh! ok i side stepped Exel this once,,

yeah, I was just commenting on the SUBSTITUTE method

 

[Aladin Akyurek]

On 2002-04-21 14:18, Chris Davison wrote:

On 2002-04-21 11:49, Jack in the UK wrote:
Chris

did you not check what i posted, i have no excel to test on quite yet but should work and will do the required results im sure. So oh! ok i side stepped Exel this once,,

yeah, I was just commenting on the SUBSTITUTE method

I'm not very happy with the SUBSTITUTE method.

It requires bookkeeping: Which cell is a true number and Which cell is a true time value?

In A1 enter: 7.2
In B1 enter: =SUBSTITUTE(A1,".",":") [ just to complicate things a bit: right-justify B1 ]
In B2 enter: 3:02
In C1 enter: =B1+0
In D1 enter: =SUBSTITUTE(B1,":",".")+0
In D2 enter: =B1+B2

See if you come out of the above jungle.

I'd rather avoid this kind of "playing around" in a serious workbook.

The OP seems to be looking for a cosmetic formatting, not for a change in underlying data type. Maybe I'am wrong on this...

Aladin