Convert Cell Formula to Convert Text to VBA Code

If the formula works and you're simply looking to keep filesize down by not repeating the formula over and over, then you can use a NAMED FORMULA without VBA.

1) Put the original value in A3.
2) Click on B3 (important, this is a relative formula so it will always look to the cell on the left for the raw data)
3) Press CTRL-F3 to open the Name Wizard
4) Create a new name as:

Name: ConvertDraftText
Scope: Workbook
RefersTo: =IF(A3=0,"",IF(A3="","",(TEXT(TRUNC((ROUNDDOWN(A3,0))),"0'-")&TEXT(((TRUNC(A3,2))-(ROUNDDOWN(A3,0)))*100,"0 ")&LOOKUP(MROUND((A3-(ROUNDDOWN(A3,0))-((TRUNC(A3,2))-(ROUNDDOWN(A3,0)))),0.0001),{0,0.0001,0.0002,0.0003,0.0004,0.0005,0.0006,0.0007,0.0008,0.0009,0.001,0.0011,0.0012,0.0013,0.0014,0.0015},{"","1/16""","1/8""","3/16""","1/4""","5/16""","3/8""","7/16""","1/2""","9/16""","5/8""","11/16""","3/4""","13/16""","7/8""","15/16"""}))))

5) Now, enter this new short formula in B3 to get your result using the named formula you created:
=ConvertDraftText


Assuming your formula actually works, it will continue to work with the very short formula reference to the named formula you have stored.

No, it will always convert the text from the cell to the left of the cell where you enter the new formula. If you enter the formula into cell M20, it will convert the text from cell L20. It's a relative formula. That's why it was important you click on B3 before you created the named formula using a relative reference to A3.

Hi Py_

It might be easier to convert the number to a string in the first place by doing an Ampersand to the number to start with.
If A1 = 3.1105 then A1&"" will be a string that has 3.1105.

Then =LEFT(A1&"",FIND(".",A1&"")-1) will be the integer
Inches will be =MID($A$1&"",FIND(".",$A$1&"")+1,2)
16th will be = =MID($A$1&"",FIND(".",$A$1&"")+3,2)

I'd do a Vlookup into your array table above to return the fraction equivalent.

This is just another way of looking at this problem that might make the whole formula a bit smaller.