I have created a Formula in Excel VBA. which Extract String containing Number from a Cell. for example a string contains : "121A Nariman Street" it will extract "121A"

Below is the Code

Function DoorNo(ADRESS)

Dim AddressArray() As String
AddressArray = Split(ADRESS)
Dim i As Integer
For i = 0 To UBound(AddressArray)
    Dim iCnt As Integer
    For iCnt = 1 To Len(AddressArray(i))
        If IsNumeric(Mid(AddressArray(i), iCnt, 1)) Then
            DoorNo = AddressArray(i)

    Next iCnt
Next i

End Function

but when there are two numbers in a strings it return second one, for example it returns "121A Nariman Street, 12th Block" it will return "12th"

i want this function to return only First Number string. how to do this ?

i am very new to Excel VBA.

  • 1
    You're missing an End If – Dave Jul 3 '17 at 14:21

If all addresses start with a number you can return the first item:

Public Function DoorNo1(ByVal address As String) As String

    DoorNo1 = Split(address)(0)

End Function

else, exit the function when the first digit is found:

Public Function DoorNo2(ByVal address As String) As String
    Dim addressArray() As String, i As Long, j As Long

    addressArray = Split(address)

    For i = 0 To UBound(addressArray)
        For j = 1 To Len(addressArray(i))
            If IsNumeric(Mid(addressArray(i), j, 1)) Then
                DoorNo2 = addressArray(i)
                Exit Function
            End If
End Function

Your question is about choosing the first time you encounter something where IsNumeric() returns true

This updated version should do it

Option Explicit
Sub doIt()
    Dim dn As String
    dn = DoorNo("12A Street 12th")
End Sub

Function DoorNo(addy As String) As String

Dim door As String
Dim AddressArray() As String
AddressArray = Split(addy)
Dim i As Integer
For i = 0 To UBound(AddressArray)

    Dim iCnt As Integer
    For iCnt = 1 To Len(AddressArray(i))    'why are you looping here?
        If IsNumeric(Mid(AddressArray(i), iCnt, 1)) Then
            door = AddressArray(i)
        End If
    Next iCnt

    If Not IsEmpty(DoorNo) Or Not DoorNo = Null Then
        DoorNo = door
        Exit For
    End If
Next i

End Function

Ignoring all the other things wrong (and keeping your existing current logic where possible), the logic you could use is to use an Exit For (as well as closing the IF statement).

Exit For will exit the For loop. You do this after the first word has been analysed and created.

Then you simply check if new first word (DoorNo) has a value or not. If it does, assign it and exit the loop

Also, since your function returns something, you should make it explicit using As

However, it makes no sense as all your current logic is checking if any character in the string is numeric and returning it if so! This means the address a12 (if it were to exist) would never be picked up. Where as my business, which doens't have a number but has the stupid name of 5tars C3entre would be accepted by your logic

Without knowing the situation, your logic, type of values you're recieving (eg, 12 House, Twelve House, 12a House, House, Other etc) it's hard to help any more but this should get you going


You don't even need to use VBA for that.

I use RegEx find/replace add-in to get regular expression functionality in Excel.
(I'm not connected to that add-in or it's author in any way, I'm just an enthusiast user.)

With that you can just use this formula:

enter image description here

Regarding your code:

quick fix you should quit from outer For loop once you've found a number

longer fix:

  • I wouldn't even use a for here, do ... loop would be more efficient (or use regular expression in your code if you're convinced to write code)
  • don't declare variable within a loop

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.