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,