VBA code to convert ft + in string to numbers

[JenniferMurphy]

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]

Hi,

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

 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

 

[Leith Ross]

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...

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]

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]

Hi,

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

 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]

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]

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:

String	Feet	Inches
3' 4.5"	3	4.5
3 ft 4.5 in	3	4.5
3ft 4.5in	3	4.5
42.1"	0	42.1
42.1 in	0	42.1
42.1in	0	42.1
3.5'	3.5	0
3.5 ft	3.5	0
3.5ft	3.5	0
3" 4'	error	error
3 4'	error	error
3' 4in	error	error
null	error	error
3 4	error	error

<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]

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/C	C	D	E	F	G
4	Valid Expressions
5	Ft-In	=cvtftin2len(C6)	=cvtlen2ftin(D6,"in")	=cvtftin2len(E6)	Comments
6	3' 6"	42	3' 6.00"	42	'-"" notation
7	1 ft 10 in	22	1' 10.00"	22	ft-in notation
8	3.5ft	42	3' 6.00"	42	Feet, no inches
9	42"	42	3' 6.00"	42	Inches, no feet
10	0' 11.999"	11.999	1' 0.00"	12	Inches rounded
11	0' 24"	24	2' 0.00"	24	Inches > 11.99
12	0'	0	0' 0.00"	0	Zero feet, no inches
13	0"	0	0' 0.00"	0	Zero inches, no feet
14	1 '2 "	14	1' 2.00"	14	Extra spaces before units
15	3'4"	40	3' 4.00"	40	No 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/C	C	D	E	F	G
18	Invalid Expressions
19	Ft-In	=cvtftin2len(C20)	=cvtlen2ftin(D20,"in")	=cvtftin2len(E20)	Comments
20	3" 4'	#VALUE!	#VALUE!	#VALUE!	Units backwards
21	3 4	#VALUE!	#VALUE!	#VALUE!	Missing units
22	3 4'	#VALUE!	#VALUE!	#VALUE!	Missing units
23	3''	#VALUE!	#VALUE!	#VALUE!	Double single quotes
24	3' 4in	#VALUE!	#VALUE!	#VALUE!	Mixed units
25	3' 4" 5	#VALUE!	#VALUE!	#VALUE!	Junk after inches
26	o"	#VALUE!	#VALUE!	#VALUE!	Non-numeric
27	3' -12"	#VALUE!	#VALUE!	#VALUE!	Negative inches
28	-3' 12"	#VALUE!	#VALUE!	#VALUE!	Negative feet
29	3' 3'	#VALUE!	#VALUE!	#VALUE!	Duplicate units (feet)
30	3" 3"	#VALUE!	#VALUE!	#VALUE!	Duplicate units (feet)
31		#VALUE!	#VALUE!	#VALUE!	Empty cell

<tbody>
</tbody>

The columns are the same as the above.