Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 19
Default Fraction to decimal

I have a cell with a value of {0'-3/4}
I want another cell to call that value and change the format to
{0'-0 3/4"}

the following code will change it to a decimal when it is formatted correctly:
which is what I am after.

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

Thanks in advance
Alvin Smith
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Fraction to decimal


You can add the text function to format the cell


{=Text(SUBSTITUTE(LEFT(H69,FIND("-",H69)-1),"'","")*12+SUBSTITUTE(REPLACE(H69,1,FIND("-",H69),""),"""",""),ANYFORMAT)}


the format would be in double quotes Like "General" or "0.00"


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196484

http://www.thecodecage.com/forumz

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 905
Default Fraction to decimal

"Alvin" wrote:
I have a cell with a value of {0'-3/4}
I want another cell to call that value and change
the format to {0'-0 3/4"}
[so] the following code will change it to a decimal
when it is formatted correctly: which is what I am after.


If you would prefer, replace your original formula with the following, which
handles all text of the form 1'-3/4", 1'-2 3/4" and 1'2":

=12*LEFT(A1,FIND(CHAR(39),A1)-1) +
(IF(ISNUMBER(FIND("/",A1)),
IF(ISNUMBER(FIND(" ",A1)),"","0 "), "") &
SUBSTITUTE(MID(A1,1+FIND("-",A1),99),CHAR(34),""))

You can replace CHAR(39) with "'" and CHAR(34) with """". I chose to use
CHAR because the string forms are difficult to read in some fonts.

Note: That is __not__ an array formula. Enter by simply pressing Enter,
not ctrl+shift+Enter. You put curly braces around the formula in your
original posting. I suspect that is your own meta-syntax to quote the
formula. But Excel uses curly braces in that way to denote an array formula.
So your intention is unclear.


----- original message -----

"Alvin" wrote:
I have a cell with a value of {0'-3/4}
I want another cell to call that value and change the format to
{0'-0 3/4"}

the following code will change it to a decimal when it is formatted correctly:
which is what I am after.

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

Thanks in advance
Alvin Smith

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I turn a fraction into a decimal. sharder Excel Worksheet Functions 0 October 15th 09 05:09 PM
How can I turn a fraction into a decimal. Megan Excel Worksheet Functions 0 October 15th 09 04:49 PM
Convert fraction to decimal Dig Excel Discussion (Misc queries) 3 June 20th 07 03:26 PM
leave the decimal fraction Darrel Excel Worksheet Functions 3 July 6th 06 06:55 AM
Help with decimal half fraction mikewild2000[_6_] Excel Programming 2 January 29th 04 02:27 PM


All times are GMT +1. The time now is 06:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"