VBA code to convert ft + in string to numbers

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
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.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,251
Office Version
2013
Platform
Windows
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:

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,865
Office Version
2010, 2007
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,498
Office Version
2010
Platform
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
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?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
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
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.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
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.
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
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
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?
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:

StringFeetInches
3' 4.5"34.5
3 ft 4.5 in34.5
3ft 4.5in34.5
42.1"042.1
42.1 in042.1
42.1in042.1
3.5'3.50
3.5 ft3.50
3.5ft3.50
3" 4'errorerror
3 4'errorerror
3' 4inerrorerror
nullerrorerror
3 4errorerror

<tbody>
</tbody>

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.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
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:

R/CCDEFG
4Valid Expressions
5Ft-In=cvtftin2len(C6)=cvtlen2ftin(D6,"in")=cvtftin2len(E6)Comments
63' 6"423' 6.00"42'-"" notation
71 ft 10 in221' 10.00"22ft-in notation
83.5ft423' 6.00"42Feet, no inches
942"423' 6.00"42Inches, no feet
100' 11.999"11.9991' 0.00"12Inches rounded
110' 24"242' 0.00"24Inches > 11.99
120'00' 0.00"0Zero feet, no inches
130"00' 0.00"0Zero inches, no feet
141 '2 "141' 2.00"14Extra spaces before units
153'4"403' 4.00"40No space between feet and inches

<tbody>
</tbody>

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:

R/CCDEFG
18Invalid Expressions
19Ft-In=cvtftin2len(C20)=cvtlen2ftin(D20,"in")=cvtftin2len(E20)Comments
203" 4'#VALUE!#VALUE!#VALUE!Units backwards
213 4#VALUE!#VALUE!#VALUE!Missing units
223 4'#VALUE!#VALUE!#VALUE!Missing units
233''#VALUE!#VALUE!#VALUE!Double single quotes
243' 4in#VALUE!#VALUE!#VALUE!Mixed units
253' 4" 5#VALUE!#VALUE!#VALUE!Junk after inches
26o"#VALUE!#VALUE!#VALUE!Non-numeric
273' -12"#VALUE!#VALUE!#VALUE!Negative inches
28-3' 12"#VALUE!#VALUE!#VALUE!Negative feet
293' 3'#VALUE!#VALUE!#VALUE!Duplicate units (feet)
303" 3"#VALUE!#VALUE!#VALUE!Duplicate units (feet)
31#VALUE!#VALUE!#VALUE!Empty cell

<tbody>
</tbody>

The columns are the same as the above.
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top