VBA code to convert ft + in string to numbers

JenniferMurphy

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,676
Office Version
  1. 365
Platform
  1. Windows
I need to write a UDF to convert a general string expression for feet and inches into two real numbers (feet and inches).

The possible string inputs can be:
  • 3' 4.5"
  • 3 ft 4.5 in
  • 3ft 4.5in
  • 42.1"
  • 42.1 in
  • 42.1in
  • 3.5'
  • 3.5 ft
  • 3.5ft

Is there a better way than a bunch of nested IF statements where I scan for the various units and split the string accordingly?

I do want to flag any invalid expressions.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hi,

One idea maybe would be to use the Split & Val functions

Code: 
 a = Split("3 ft 4.5 in", "ft")    
    For Each Item In a
        MsgBox Val(Item)
    Next

Val returns the numbers contained in a string as a numeric value of appropriate type.
The Val function stops reading the string at the first character it does not recognize as part of a number so in this example you should get just 3 & 4.5 values returned - invalid entries 0 is returned.


Dave
 
Last edited:
Upvote 0
Hello Jennifer,

The macro FeetAndInches is a UDF. It will return both values to the cells to the right of the UDF when successful. If not then the cell's font will be changed to red.

I have included a second macro TestA which will check all the cells with values in column "A" using the UDF macro. Let me know if this needs any adjustments.

Copy this code to a separate VBA Module...

Code: 
Public RegExp As Object


Function FeetAndInches(ByRef Rng As Range) As Variant
        
    Dim nArray(1) As Variant
    
        If RegExp Is Nothing Then
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Global = True
            RegExp.IgnoreCase = True
            RegExp.Pattern = "(\d+\.?\d*)\s*(?:ft|in|'|"")"
        End If
    
        Set Cell = Rng.Cells(1, 1)
        
        RegExp.Pattern = "(\d+\.?\d*)\s*(?:ft|')"
        Set Matches = RegExp.Execute(Rng)
        
        If Matches.Count <> 0 Then nArray(0) = Val(Matches(0).SubMatches(0))
        
        RegExp.Pattern = "(\d+\.?\d*)\s*(?:in|"")"
        Set Matches = RegExp.Execute(Rng)


        If Matches.Count <> 0 Then nArray(1) = Val(Matches(0).SubMatches(0))
        
        If nArray(0) = Empty And nArray(1) = Empty Then
            Cell.Font.Color = vbRed
        Else
            FeetAndInches = nArray
        End If
        
End Function


Sub TestA()


    Dim Cell    As Range
    Dim Results As Variant
    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet
    
        Set Wks = ActiveSheet
        
        Set RngBeg = Wks.Range("A1")
        Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)
        
        If RngEnd.row < RngBeg.row Then Exit Sub
        
            For Each Cell In Wks.Range(RngBeg, RngEnd)
                Results = FeetAndInches(Cell)
                If Not IsEmpty(Results) Then
                    Cell.Offset(0, 1) = Results(0)
                    Cell.Offset(0, 2) = Results(1)
                End If
            Next Cell
        
End Sub
 
Upvote 0
JenniferMurphy said:
I need to write a UDF to convert a general string expression for feet and inches into two real numbers (feet and inches).

The possible string inputs can be:
  • 3' 4.5"
  • 3 ft 4.5 in
  • 3ft 4.5in
  • 42.1"
  • 42.1 in
  • 42.1in
  • 3.5'
  • 3.5 ft
  • 3.5ft
Click to expand...
I am not clear on what you want for the results. Did you want feet in one column and inches in the next? What should happen to 42"... should it stay as 32 or become 3 feet and 6 inches?
 
Upvote 0
dmt32 said:
Hi,

One idea maybe would be to use the Split & Val functions

Code: 
 a = Split("3 ft 4.5 in", "ft")    
    For Each Item In a
        MsgBox Val(Item)
    Next

Val returns the numbers contained in a string as a numeric value of appropriate type.
The Val function stops reading the string at the first character it does not recognize as part of a number so in this example you should get just 3 & 4.5 values returned - invalid entries 0 is returned.


Dave
Click to expand...

I am using Split, but was not aware of Val. After looking at it, I don't think it will work because of it's bizarre habit of ignoring spaces. The result of [Val("12 34")] is 1234, not 12. Val would be really useful if it didn't have that trait and if it returned all of the numbers in the string in an array, like Split does.

Thanks for the suggestion.
 
Upvote 0
Leith Ross said:
Hello Jennifer,

The macro FeetAndInches is a UDF. It will return both values to the cells to the right of the UDF when successful. If not then the cell's font will be changed to red.

I have included a second macro TestA which will check all the cells with values in column "A" using the UDF macro. Let me know if this needs any adjustments.
Click to expand...
Wow!!! That's impressive. I am afraid that it is beyond my skill level to comprehend without more srudy than I have time for right now. I'll put it on my todo list.

Thanks very much
 
Upvote 0
Rick Rothstein said:
I am not clear on what you want for the results. Did you want feet in one column and inches in the next? What should happen to 42"... should it stay as 32 or become 3 feet and 6 inches?
Click to expand...

I want to parse the expression to extract the numeric values for feet and inches into VBA variables that I can then use to do arithmetic. This will all be in VBA code in a UDF, so no rows or columns involved.

For example, if the input string is is sFtIn, then I'd like the numeric values for the feet and inches in nFt & dIn. Here are the results for the examples I gave:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]String[/TD]
[TD="align: center"]Feet[/TD]
[TD="align: center"]Inches[/TD]
[/TR]
[TR]
[TD="align: center"]3' 4.5"[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4.5[/TD]
[/TR]
[TR]
[TD="align: center"]3 ft 4.5 in[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4.5[/TD]
[/TR]
[TR]
[TD="align: center"]3ft 4.5in[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4.5[/TD]
[/TR]
[TR]
[TD="align: center"]42.1"[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]42.1[/TD]
[/TR]
[TR]
[TD="align: center"]42.1 in[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]42.1[/TD]
[/TR]
[TR]
[TD="align: center"]42.1in[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]42.1[/TD]
[/TR]
[TR]
[TD="align: center"]3.5'[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3.5 ft[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3.5ft[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3" 4'[/TD]
[TD="align: center"]error[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]3 4'[/TD]
[TD="align: center"]error[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]3' 4in[/TD]
[TD="align: center"]error[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]null[/TD]
[TD="align: center"]error[/TD]
[TD="align: center"]error[/TD]
[/TR]
[TR]
[TD="align: center"]3 4[/TD]
[TD="align: center"]error[/TD]
[TD="align: center"]error[/TD]
[/TR]
</tbody>[/TABLE]

If there are values for both feet and inches, use them. If there is a value for just one, use that and assign zero to the other.
 
Upvote 0
I needed this conversion, so I spent some time and wrote a (fairly) general UDF. I'll provide it here in case it's of use to anyone else.

If you find any bugs or have any suggestions for improvements, please let me know.

The code is fairly complicated, so I've put it in a code module in a workbook with examples of its use. That workbook can be found here:

https://www.dropbox.com/s/vqhzsflnstgmkwq/CvtLen2FtIn (no add-in).xlsm?dl=0

This table shows some examples of conversions it can handle:

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="colspan: 5, align: center"]Valid Expressions[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Ft-In[/TD]
[TD="align: center"]=cvtftin2len(C6)[/TD]
[TD="align: center"]=cvtlen2ftin(D6,"in")[/TD]
[TD="align: center"]=cvtftin2len(E6)[/TD]
[TD="align: center"]Comments[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]3' 6"[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]3' 6.00"[/TD]
[TD="align: center"]42[/TD]
[TD]'-"" notation[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]1 ft 10 in[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]1' 10.00"[/TD]
[TD="align: center"]22[/TD]
[TD]ft-in notation[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]3.5ft[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]3' 6.00"[/TD]
[TD="align: center"]42[/TD]
[TD]Feet, no inches[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]42"[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]3' 6.00"[/TD]
[TD="align: center"]42[/TD]
[TD]Inches, no feet[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]0' 11.999"[/TD]
[TD="align: center"]11.999[/TD]
[TD="align: center"]1' 0.00"[/TD]
[TD="align: center"]12[/TD]
[TD]Inches rounded[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]0' 24"[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]2' 0.00"[/TD]
[TD="align: center"]24[/TD]
[TD]Inches > 11.99[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]0'[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0' 0.00"[/TD]
[TD="align: center"]0[/TD]
[TD]Zero feet, no inches[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]0"[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0' 0.00"[/TD]
[TD="align: center"]0[/TD]
[TD]Zero inches, no feet[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]1 '2 "[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]1' 2.00"[/TD]
[TD="align: center"]14[/TD]
[TD]Extra spaces before units[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]3'4"[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]3' 4.00"[/TD]
[TD="align: center"]40[/TD]
[TD]No space between feet and inches[/TD]
[/TR]
</tbody>[/TABLE]

Col C is the input.
Col D is the input converted to inches.
Col E is that result converted back to feet & inches.
Col F is that result converted back to inches.

This table shows some of the errors it can handle:

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="colspan: 5, align: center"]Invalid Expressions[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]Ft-In[/TD]
[TD="align: center"]=cvtftin2len(C20)[/TD]
[TD="align: center"]=cvtlen2ftin(D20,"in")[/TD]
[TD="align: center"]=cvtftin2len(E20)[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]3" 4'[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Units backwards[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]3 4[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Missing units[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"]3 4'[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Missing units[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"]3''[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Double single quotes[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"]3' 4in[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Mixed units[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"]3' 4" 5[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Junk after inches[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"]o"[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Non-numeric[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"]3' -12"[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Negative inches[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]-3' 12"[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Negative feet[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]3' 3'[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Duplicate units (feet)[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]3" 3"[/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Duplicate units (feet)[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Empty cell[/TD]
[/TR]
</tbody>[/TABLE]

The columns are the same as the above.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Remove text from string between parenthesis leaving only numbers
2
Replies
16
Views
2K
Peter_SSs
Peter_SSs
P
  • Question Question
How to convert Feet and Inches-String to inches only, using VBA ?
Replies
7
Views
3K
Phuynh
P
JenniferMurphy
  • Question Question
UDF to convert a value to feet & inches
Replies
0
Views
775
JenniferMurphy
JenniferMurphy
K
  • Question Question
VBA Code To Parse and Evaluate Various Feet and Inch Notations For Peer Review
Replies
1
Views
3K
Kcd2018
K
C
  • Question Question
Using Do-While Loop to return a value based on a range of inputs returns unexpected results
Replies
0
Views
448
chirp08
C

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top