Convert Feet - Inch - Inch Fraction string into numerical value

**plans** · 03-25-2017, 07:01 PM

Hello,
I have a string in a cell which looks like:
5’ – 4”
Or,
11’-6 11/16”
Or,
2’- 0 1/8”
And so forth... (values might be different every time)
I need to pull out the numerical values from these strings in order to operate and get the arithmetical values of those measurements.
Here is what I am struggling with:
1. Get the numerical value of the string preceding the “ ‘ “ sign.
2. Get a message box : String contains “/” sign when there is one.
3. Get the numerical value between the “-“ sign and the “ “ “ sign when no “/” exists
4. Get the numerical value between the “-“ sign and the fraction following the last gap
5. Get the numerical value of the fraction following the last gap, preceding the " " " sign when “/” exists.
Or, any other idea to extract the numerical value of the string , would be appreciated.
Perhaps there is a routine already for this?
Thanks

**mikerickson** · 03-25-2017, 07:39 PM

Does 5'-4" represent 64 inches (5 ft. 4 inches) or is it 56 inches (5 ft. less 4 inches)?

**plans** · 03-25-2017, 07:44 PM

5'-4" is 64"
The "-" sign is just a formatting / marker to separate the Feet from Inches
Thanks

**mikerickson** · 03-25-2017, 07:55 PM

This UDF will do what you want.

Function FInch2Inch(aString As String) As Double
    Dim footPart As Double
    Dim strIn As String
    If Not (aString Like "*'*") Then aString = "'" & aString
    aString = Replace(aString, "-", vbNullString)
    footPart = Val(aString)
    
    FInch2Inch = 12 * footPart + Evaluate(Replace(Split(aString, "'")(1), """", vbNullString))
End Function

**plans** · 03-25-2017, 08:23 PM

mikereickson,
Thanks for the response; I am not quite familiar with UDF.
How do I call the function in my subroutine?

Is it something like
NumValue = FInch2Inch(Activecell.value) ?
Thanks

**mikerickson** · 03-25-2017, 11:29 PM

The argument passed to FInch2Inch is a string, and the value it returns is a type double.

If you have 5'-6 3/4" in A1, a cell with the formula =FInch2Inch(A1) will return 66.75

Convert Feet - Inch - Inch Fraction string into numerical value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Convert Feet - Inch - Inch Fraction string into numerical value

Re: Convert Feet - Inch - Inch Fraction string into numerical value

Re: Convert Feet - Inch - Inch Fraction string into numerical value

Re: Convert Feet - Inch - Inch Fraction string into numerical value

Re: Convert Feet - Inch - Inch Fraction string into numerical value

Re: Convert Feet - Inch - Inch Fraction string into numerical value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] formula to convert decimal feet to feet inch sixteenths

decimal feet to feet-inch-sixteenths conversion

Converting feet & inch demensions to inch only in excell

Formula to round value (in feet) up to nearest 1/16 inch.

[SOLVED] Decimal to feet/inch/fractional inch conversion

Professional engineering graph sheets 10 x 10/inch and 8 x 8/inch

[SOLVED] convert decimal to inch & fraction

Bookmarks

Bookmarks

Posting Permissions