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

Trebor8484

Board Regular
Joined
Oct 27, 2018
Messages
69
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
Code:
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
 
Upvote 0
Assuming your data is in column A starting at row 1, try:
Code:
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
 
Upvote 0
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:))?
 
Last edited:
Upvote 0
Another option, while the OP answers Rick's question

Code:
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
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
If they have no problem, we could adjust it in this way

Code:
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
 
Upvote 0
If they have no problem, we could adjust it in this way

Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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