Macro Converts Fractions Into Dates

**thensley** · 01-12-2011, 11:00 AM

In a "Bill of Materials" spreadsheet, I am trying to create a macro that will calculate weight. One of the cells will have an entry like: PL. 1/2"X12". My macro uses both dimensions in this entry as well as a few other cell values. I know that I can put a "0 " in front of the fraction, but that creates two other problems: if the fraction is greater than 1" (1 1/2" becomes 0 1 1/2") it fails, and I can't persuade the key people here to allow the BOM to have a "0 " in front of fractions. So my question is, is there a way to prevent XL from converting fractions into dates in a macro besides adding a "0 "?

=VALUE(TRIM(SUM(SUBSTITUTE(SUBSTITUTE(LEFT(D32, FIND("X", D32,1)-1),"""",""),"PL. ","")+0))) * VALUE(TRIM(SUBSTITUTE(MID(D32, FIND("X",D32,1)+1, 5),"""",""))) * (C32) * (F32+(E32*12)) * 0.2836

Most of this macro is dedicated to isolating the two dimensions in the description (D32) (PL. 1/2"X12")

Thank you for any suggestions,

**DonkeyOte** · 01-12-2011, 11:35 AM

You refer to Macros but are posting a Formula - from a formula perspective you might try adapting along the lines of:

=(REPT("0 ",ISERR(SEARCH("* * */*",D32))) & REPLACE(SUBSTITUTE(LEFT(D32,FIND("X",D32)-1),"""",""),1,FIND(" ",D32),"")) * SUBSTITUTE(REPLACE(D32,1,FIND("X",D32),""),"""","") * C32 * (F32+(E32*12)) * 0.2836

Note - given the multiplication taking place the use of VALUE is unnecessary as both sides will coerce automatically.