Format-How to Force Fraction to DECIMAL?

J

JackBean

Active Member
Joined
Nov 1, 2007
Messages
403
Format-How to Force Fraction to DECIMAL?

The formula in B2 gives "1/3".
The formula in B1 gives "39085"
The formula in B3 is a way around this and gives "0.333333", but I am curious as to how to get B1 or B2 to work. Thanks for any ideas.

Code: 
  A      B        
1 1/3BaC 39085    
2 1/3BaC 1/3      
3 1/3BaC 0.333333 
Sheet1
[Table-It] version 09 by Erik Van Geit
Code: 
RANGE FORMULA 
B1    =--LEFT(A1,FIND("/",A1)+SUM(--ISNUMBER(-MID(A1,FIND("/",A1)+1,ROW(INDIRECT("1:"&LEN(A1)))))))
B2    =LEFT(A2,FIND("/",A2)+SUM(--ISNUMBER(-MID(A2,FIND("/",A2)+1,ROW(INDIRECT("1:"&LEN(A2)))))))
B3    =LEFT(A3,FIND("/",A3)-1)/MID(A3,FIND("/",A3)+1,SUM(--ISNUMBER(-MID(A3,FIND("/",A3)+1,ROW(INDIRECT("1:"&LEN(A3)))))))
[Table-It] version 09 by Erik Van Geit
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
I don't understand what you need in B1.
As to B2, why don't you leave the formula as it is, and just format the cell as number, with 6 decimals?
 
Upvote 0
I am looking at possibilities for shortening formulas. In this case the goal is to extract the number, which could be an integer or decimal or fraction, as a decimal or number. The desired result for 1/2CaB in A6 is 0.5 in C6.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
I have since found that "regular expressions" (reference link below) can be applied to come up with a shorter formula.<o:p></o:p>
<o:p></o:p>
=RegExpFind(A1,"[\d/.]{1,}") in column B extracts the first integer or decimal or fraction as string.<o:p></o:p>
<o:p></o:p>
=--RegExpFind(A1,"[\d/.]{1,}") in column C changes the text to a number, unless it is a fraction, in which case for 1/2 a date number comes up in C2: 39449. Changing cell format to number gives 39449.00 in C3.<o:p></o:p>
<o:p></o:p>
The numbers before and after the "/" can be extracted and divided to give a number with =--IF(ISERROR(FIND("/",A5)),RegExpFind(A5,"[\d.]{1,}"),RegExpFind(A5,"[\d]{1,}")/RegExpFind(RIGHT(A5,LEN(A5)-FIND("/",A5)),"[\d/]{1,}")). But I am curious if there is some trick to getting =--RegExpFind(A1,"[\d/.]{1,}") to work in this case.<o:p></o:p>
<o:p></o:p>
Rich (BB code): 
  A      B   C
Rich (BB code): 
1 0.5BaC 0.5 0.5      
2 1/2BaC 1/2 39449    
3 1/2BaC 1/2 39449.00 
Sheet1
[Table-It] version 09 by Erik Van Geit
Rich (BB code): 
RANGE FORMULA (1st cell)
B1:B3 =RegExpFind(A1,"[\d/.]{1,}")
C1:C3 =--RegExpFind(A1,"[\d/.]{1,}")
[Table-It] version 09 by Erik Van Geit
<o:p></o:p>
<o:p>
Rich (BB code): 
  A      B   C
Rich (BB code): 
5 0.5BaC 0.5 0.5 
6 1/2BaC 0.5 0.5 </o:p>
<o:p>Sheet1</o:p>
<o:p>[Table-It] version 09 by Erik Van Geit
Rich (BB code): 
RANGE FORMULA (1st cell)
B5:B6 =IF(ISERROR(FIND("/",A5)),RegExpFind(A5,"[\d.]{1,}"),RegExpFind(A5,"[\d]{1,}")/RegExpFind(RIGHT(A5,LEN(A5)-FIND("/",A5)),"[\d/]{1,}"))
C5:C6 =--IF(ISERROR(FIND("/",A5)),RegExpFind(A5,"[\d.]{1,}"),RegExpFind(A5,"[\d]{1,}")/RegExpFind(RIGHT(A5,LEN(A5)-FIND("/",A5)),"[\d/]{1,}"))</o:p>
<o:p>[Table-It] version 09 by Erik Van Geit
</o:p>
<o:p></o:p>
<o:p></o:p>
MrExcel post<o:p></o:p>
December 10, 2007<o:p></o:p>
Re: TEXT String MID Help needed<o:p></o:p>
http://www.mrexcel.com/forum/showthread.php?t=289617<o:p></o:p>
<o:p></o:p>
 
Upvote 0
Hi

What you want is to evaluate a string. You use a UDF, ex.:

Code: 
Function GetNumber(s As String)
 
With CreateObject("vbscript.regexp")
    .Pattern = "\d+([/.]\d+)?"
    If .test(s) Then
        GetNumber = Evaluate(CStr(.Execute(s)(0)))
    Else
        GetNumber = "No number found"
    End If
End With
End Function

You can use it in the worksheet like any other function, ex.:

<table><tr><td><table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1/2CaB </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">12/5CaB </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2.4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">13/100xx</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">0.13</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">2cd</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">2.34xxx</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2.34</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">ff</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">No number found</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1.xls]Sheet2</td></tr></table><br>
</td>
<td style="padding-left:5em;" >
<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >Addr</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >Formula</td></tr><tr><td colspan=2 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; " >[Book1.xls]Sheet2</td></tr><tr><td rowspan=2 style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center; " >B1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em; " > =getnumber(A1) </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;; " > Copy down</td></tr></table>
<br></td></tr></table>

Remark: you could use native excel formulas and evaluate the result via a name but a UDF is simpler.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Variable number formatting formula
Replies
7
Views
221
Joe4
Joe4
S
  • Question Question
How do I sum without knowing the range?
Replies
1
Views
297
sGraham24
S
W
  • Solved
Conditional Formatting of 2 x cells based on very close match?
Replies
7
Views
557
Joe4
Joe4
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
181
SBExcel123
S
G
  • Question Question
Creating an array of Indirect Sums?
Replies
1
Views
557
Eric W
Eric W

Forum statistics

Threads
1,223,676
Messages
6,173,770
Members
452,534
Latest member
autodiscreet

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top