vba find first set of numbers in a string ignoring any subsequent

[Trebor8484]

Hi,

I'm looking for a solution to the following issue please.

I am importing data into excel from a web application where some of the data is in "free text" format.

I am trying to extract the first group of numeric values from a string, so for example cell in cell A1 if the value is something like "Hello, my name is Scott and I live at 123 Maiden Road, I've lived here for the last 23 years" - so in this I would need to see 123 in the adjacent cell, ignoring the "23" part of the string.

I know this could probably be done with a formula, but a vba solution would be better so I can include it with the rest of my project.

Thanks

 

[Rick Rothstein]

Hi, no floating point numbers, so 12.1 should be 12. Also if possible, is there a way of identifying cells that have multiple sets of numbers within the string? I would still need to output just the first set, but then a way of identifying any anomalies.

In that case, what I posted in Message #13 should work fine and so should the code Dante posted in Message #19 .

 

[Trebor8484]

With regards my other point for cells with multiple sets of numbers, this seems to work but can someone please check to see if it looks ok or if there is a better way of writing it? parseNum counts the number of integers in string then is compared to the output cells length. If it matches then it returns yes to identify only a single set of numbers present in the string.

Sub extnum()


    Dim c As Range, n As Long, j As Long
    Dim StrTest As String, i As Integer


    For Each c In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        n = Evaluate("=min(FIND({0,1,2,3,4,5,6,7,8,9}," & c.Address & "&"" 0123456789""))")
        j = n + 1
        Do While Mid(c, j, 1) Like "*[0-9]*"
            j = j + 1
        Loop
        c.Offset(0, 1).Value = Mid(c, n, j - n)
        StrTest = c.Value
        If Len(c.Offset(0, 1).Value) = parseNum(StrTest) Then
            c.Offset(0, 2).Value = "Yes"
        Else
            c.Offset(0, 2).Value = "No"
        End If
    Next c
        
End Sub


Function parseNum(StrSearch As String) As Integer


    Dim i As Integer, NumCount As Integer
    
    For i = 1 To Len(StrSearch)
        If IsNumeric(Mid(StrSearch, i, 1)) Then NumCount = NumCount + 1
    Next


    parseNum = NumCount


End Function

 

[Rick Rothstein]

With regards my other point for cells with multiple sets of numbers, this seems to work but can someone please check to see if it looks ok or if there is a better way of writing it? parseNum counts the number of integers in string then is compared to the output cells length. If it matches then it returns yes to identify only a single set of numbers present in the string.

You can eliminate the parseNum function and use this modified version of the code you posted instead...

Sub extnum()

    Dim c As Range, n As Long, j As Long
    Dim StrTest As String, i As Integer

    For Each c In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        n = Evaluate("=min(FIND({0,1,2,3,4,5,6,7,8,9}," & c.Address & "&"" 0123456789""))")
        j = n + 1
        Do While Mid(c, j, 1) Like "*[0-9]*"
            j = j + 1
        Loop
        c.Offset(0, 1).Value = Mid(c, n, j - n)

[B][COLOR="#0000FF"]        If c.Value Like "*#[!0-9]*#*" Then
            c.Offset(0, 2).Value = "No"
        Else
            c.Offset(0, 2).Value = "Yes"
        End If
[/COLOR][/B]

    Next c
        
End Sub