Formula to Convert Fractions to Decimals in Excel

I’ve been working to get a product information file into a format that would allow me to import it into my database and one of the issues I’ve encountered is that this file contains three columns for dimensions: height, width, length. These three contain fractional values in the format “6 1/16” which Excel doesn’t translate well. So, I put together this formula to deal with that column:

=IF(LEN(TRIM(A2)) >0,IF(TRIM(A2)-INT(TRIM(A2)) > 0,INT(LEFT(A2,FIND(” “,A2)))+(INT(MID(A2,FIND(” “,A2),FIND(“/”,A2)-FIND(” “,A2)))/INT(MID(A2,FIND(“/”,A2)+1,LEN(TRIM(A2))-FIND(“/”,A2)+1))),TRIM(A2)),””)

You’ll note that the value I’m looking for is in column A, row 2 and that if the value of A2 is blank I’m letting the result be blank. If you’d prefer the result to be 0 if there is no value in the fractions column, you can change the “” to 0.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.