Find first string in text string that is numbers only. Formulas only

Question

Is it possible to find the first string within a string that contains only numbers/digits? I have a few thousand addresses to process and some of them have Apartment numbers. I need to find the address number and extract it but the apartment number get extracted instead.

I was able to extract the first number string with this Formula, OneToZero is {1,2,3,4,5,6,7,8,9,0}

=MID($A1,MIN(IFERROR(FIND(OneToZero,$A1),"")),FIND(" ",$A1,MIN(IFERROR(FIND(OneToZero,$A1),"")))-MIN(IFERROR(FIND(OneToZero,$A1),""))-0)

Returns this.

1417 Horne RD #1445A (Building 15) ----- 1417(Correct)
Westwood #104A 801 Cantwell Ln --------- 104A(wrong) 801(Correct)

I tried using

=MID($A1,MIN(IFERROR(FIND(" "&OneToZero," "&$A1),"")),FIND(" ",$A1,MIN(IFERROR(FIND(" "&OneToZero," "&$A1),"")))-MIN(IFERROR(FIND(" "&OneToZero," "&$A1),""))-0)

But returns this if the first number doesn't have a non-digit character in front.

Westwood 104A 801 Cantwell Ln --------- 104A(wrong) 801(Correct)

Cannot just remove the first string using the "#" due to it may say "Apt#" or just "104A".

Thank you.

score 1 · Answer 1 · answered Apr 12 '16 at 03:15

I found this to test the String

=IF(AND(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))),A1,"")

and placed the code to locate the first string and second string.

Here is the entire thing. I don't know much about this, just pieced a bunch of different little snippets. I am now hoping that someone can help me streamline this.

    =IF(AND(ISNUMBER(--(MID(

MID([@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")),FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")))-MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))-0),ROW(INDIRECT("1:"&LEN(MID([@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")),FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")))-MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))-0)))),1)))),

MID([@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")),FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),"")))-MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))-0),

MID([@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))+0)),"")),FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))+0)),""))),"")))-MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin],FIND(" ",[@Origin],MIN(IFERROR(FIND(" "&OTZ," "&[@Origin]),""))+0)),""))),""))-0))

It is very long. I changed "OneToZero" is {1,2,3,4,5,6,7,8,9,0} to OTZ. [@Origin] is the column where the "raw data"(Unformatted Addresses) is.

Raystafarian · Answer 2 · 2016-04-12T18:07:30.830

Since you asked for an example of a VBA solution, this will print the first only numeric sub-string in the cell to the right -

Option Explicit

Public Sub ExtractStreetNumber()
    Application.ScreenUpdating = False
    Const nullCharacter As String = " "
    Dim subString As String
    Dim fullAddress As String
    Dim subStringArray() As String
    Dim arrayPosition As Long
    Dim testCell As Range
    Dim addressTestRange As Range
    Set addressTestRange = Range("A1:A3") 'put your range of addresses here

    For Each testCell In addressTestRange
        fullAddress = testCell
        subStringArray = Split(fullAddress, nullCharacter)
        For arrayPosition = 0 To UBound(subStringArray)
            subString = subStringArray(arrayPosition)
            If IsNumeric(subString) Then
            testCell.Offset(, 1) = subString
            GoTo NextIteration
            End If
        Next
NextIteration:
    Next
    Application.ScreenUpdating = True
End Sub

You could also create a user-defined function to use on the worksheet like ExtractStreetNumber(A1) -

Option Explicit

Public Function ExtractStreetNumber(ByVal fullAddress As String) As Long

    Const nullCharacter As String = " "
    Dim subString As String
    Dim subStringArray() As String
    Dim arrayPosition As Long

        subStringArray = Split(fullAddress, nullCharacter)
        For arrayPosition = 0 To UBound(subStringArray)
            subString = subStringArray(arrayPosition)
            If IsNumeric(subString) Then
                ExtractStreetNumber = subString
            Exit Function
            End If
        Next

End Function

score 0 · Answer 3 · edited Mar 20 '17 at 10:17

Here's another way to do it with a formula, but you need to text to columns it and I'm blatantly ripping off Scott's answer to a different question.

First I'd copy the data to a new sheet.

Go to data - data tools - text to columns - delimited - space

Now you can find the first only numeric substring in each row with something like

=INDIRECT(ADDRESS(ROW(),MIN(IF(ISNUMBER(A1:F1),COLUMN(A1:F1)))))

It's an array formula so you need to use Ctrl+Shft+Entr and make sure it gets curly brackets.

Now you can copy - paste special the number back to the original sheet.

Find first string in text string that is numbers only. Formulas only

3 Answers3