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]

Here is a function that you can use... simply pass in the text as a quoted string, a string variable or a cell reference and it will return the first number if finds in that text.

Function FirstNumber(InText As String) As Double
  Dim X As Long
  For X = 1 To Len(InText)
    If IsNumeric(Mid(InText, X, 1)) Then
      FirstNumber = Val(Mid(InText, X))
      Exit Function
    End If
  Next
End Function

 

[mumps]

Assuming your data is in column A starting at row 1, try:

Sub findNum()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, i As Long, splitRng As Variant
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("A1:A" & LastRow)
        splitRng = Split(rng, " ")
        For i = LBound(splitRng) To UBound(splitRng)
            If IsNumeric(splitRng(i)) Then
                rng.Offset(0, 1) = splitRng(i)
                Exit For
            End If
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Trebor8484]

Thanks both, both solutions work perfectly

 

[Rick Rothstein]

Thanks both, both solutions work perfectly

You are welcome, of course, but I have a follow up question for you regarding how "perfectly" our codes actually are. Will this number our codes are finding ever be attached to other text (other than a space, parentheses, and such)? I ask because if your text had something like this in it...

Text 12e3 more text

both of our codes would interpret the first number to be 12000 as 12e3 is how VB and Excel write power of ten numbers (in this example, 12 times 10 raised to the 3rd power which is the same as 12000). Also, if your text was something like this...

Text 12xyz34 more text 987 more text

my code would interpret the return 12 as that is the first number it sees whereas mumps' code looks at the entire combination 12xyz34 and skips it because it is not a number and, thus, his code returns 987. And what about number with commas in them? If your text was something like this...

Text 12,345 more text

my code would return 12 (as the Val function I use does not recognize thousands separators) where as mumps' code would return 12,345 as the IsNumeric function sees that as a valid number.

So, do you ever have numbers like any of the above and, if so, what results do you want to see for them? We can fix our codes to meet whatever your needs are, we just need to know exactly what those needs are. Also, are you looking for a function type solution that can be called as needed or a macro solution that can be applied to a range of cells as mumps' code did (your statement about needing VB code left this up to interpretation, obviously)?

 

[DanteAmor]

Another option, while the OP answers Rick's question

Function ExtractNumber(c As Range)
 d = c.Address 
 ExtractNumber = Val(Evaluate("=MID(" & d & ",MIN(FIND({0,1,2,3,4,5,6,7,8,9}," & d & "&""0123456789"")),LEN(" & d & "))"))
End Function

 

[Trebor8484]

You are welcome, of course, but I have a follow up question for you regarding how "perfectly" our codes actually are. Will this number our codes are finding ever be attached to other text (other than a space, parentheses, and such)? I ask because if your text had something like this in it...

Text 12e3 more text

both of our codes would interpret the first number to be 12000 as 12e3 is how VB and Excel write power of ten numbers (in this example, 12 times 10 raised to the 3rd power which is the same as 12000). Also, if your text was something like this...

Text 12xyz34 more text 987 more text

my code would interpret the return 12 as that is the first number it sees whereas mumps' code looks at the entire combination 12xyz34 and skips it because it is not a number and, thus, his code returns 987. And what about number with commas in them? If your text was something like this...

Text 12,345 more text

my code would return 12 (as the Val function I use does not recognize thousands separators) where as mumps' code would return 12,345 as the IsNumeric function sees that as a valid number.

So, do you ever have numbers like any of the above and, if so, what results do you want to see for them? We can fix our codes to meet whatever your needs are, we just need to know exactly what those needs are. Also, are you looking for a function type solution that can be called as needed or a macro solution that can be applied to a range of cells as mumps' code did (your statement about needing VB code left this up to interpretation, obviously)?

Hi Rick, I was looking for a macro so initially tried with mumps code, but managed to use your UDF after a bit of fiddling to get it to work in my sub.

Numbers should be proceeded by a space, but as its "free text" I suppose any eventuality is possible so 12E3 should be just 12, 12xyz34 should just be 12 and 12,345 should also be 12.

I've just ran this on 10,000 lines of data and haven't come accross any issue yet, but I would like to cover for any eventuality. A UDF is fine now I know how to call it in my sub.

I'm calling it like this, not sure if there is a better way? Also please let me know if there is a revision needed to your code.

I had to declare a string variable to hold the cell value, for some reason I couldn't just use c.value when calling your UDF.

Sub test()


    Dim c As Range
    Dim MyString As String
    
    For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        MyString = c
        c.Offset(0, 1).Value = FirstNumber(MyString)
    Next c
    
End Sub

 

[DanteAmor]

If they have no problem, we could adjust it in this way

Sub test()
  Dim c As Range
  For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    c.Offset(0, 1).Value = Val(Mid(c, Evaluate("=MIN(FIND({0,1,2,3,4,5,6,7,8,9}," & c.Address & "&""0123456789""))")))
  Next c
End Sub

 

[Rick Rothstein]

If they have no problem, we could adjust it in this way

Sub test()
  Dim c As Range
  For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    c.Offset(0, 1).Value = Val(Mid(c, Evaluate("=MIN(FIND({0,1,2,3,4,5,6,7,8,9}," & c.Address & "&""0123456789""))")))
  Next c
End Sub

...both of our codes would interpret the first number to be 12000...

Numbers should be proceeded by a space, but as its "free text" I suppose any eventuality is possible so 12E3 should be just 12

Your code does what my code does for 12e3 but that is not what the OP wants.

 

[DanteAmor]

Your code does what my code does for 12e3 but that is not what the OP wants.

I know, the 2 codes do the same.
I was just delivering another shorter option!