Prev: "Move to bottom of range"
Next: Assign variable value across files
From: Alvin on 18 Apr 2010 12:50
I want to have one cell call a cell that has a fraction in it that is
formated like so
0'-7/8"
I need it to be formated like
0'-0 7/8" so with the following code I can convert it to decimal.

[=SUBSTITUTE(LEFT(H69,FIND("-",H69)-1),"'","")*12+SUBSTITUTE(REPLACE(H69,1,FIND("-",H69),""),"""","")]

the above code works when it is formated correctly

Please help
Thanks in advance
From: ker_01 on 19 Apr 2010 10:47
Is this a custom cell format, or just content stored as the text default?

if text, why not just search/replace "-" with "- 0 "

Or if you have mixed cells (you didn't provide much detail), use code to
find cells where [logic, not code] =Len
((find("/",string))-(find("-",string)))< 3
and just replace in those cells

HTH
Keith


"Alvin" wrote:

> I want to have one cell call a cell that has a fraction in it that is
> formated like so
> 0'-7/8"
> I need it to be formated like
> 0'-0 7/8" so with the following code I can convert it to decimal.
>
> [=SUBSTITUTE(LEFT(H69,FIND("-",H69)-1),"'","")*12+SUBSTITUTE(REPLACE(H69,1,FIND("-",H69),""),"""","")]
>
> the above code works when it is formated correctly
>
> Please help
> Thanks in advance
 | 
Pages: 1
Prev: "Move to bottom of range"
Next: Assign variable value across files