Split Full Addresses without comma's

I am a Website Developer and DBA Admin, I have been messing around in VBA for a few months now and decided to create a few functions to split a full address that is in one cell into multiple cells (Street, City, State, Zip, Zip + 4)

Here is a example:

Full Address Column
9252 Palomino Crest Dr Alpine CA 92045 

Street Column
9252 Palomino Crest Dr

City Column
Alpine

State Column
CA

Zip Column 
92045

So far all my code is still under development. It is in no way near perfect or complete yet.

I am looking to get user feedback and tips on how to make it simpler. I have not seen any one come up with some functions to do this process. All solutions I have seen are based off if the address as commas. I gathered what I can from this site and Google to figure out how to put all this together. I am open to all thoughts and criticism.

READ NOTES: These functions are based on a few things;
1. The full address column must be all upper case.
2. Make sure there are no commas or hyphen. # is accepted as some addresses have apartment number, etc

Known Errors:
1. Addresses outside of United States turn up errors
2. Addresses without Street Abbreviation (ie: St, Dr, Loop, Ridge)
3. Addresses with random numbers lol i'm still working the bugs out.

TO USE:
Street - Place =street(a2) into column
City - Place =city(a2) into column
State - Place =state(a2) into column
Zip - Place =zip(a2) into column
Zip + 4 - Place =zip4(a2) into column


Code will be posted in next post...

=Street(a2) Function:

Function street(rng As Range)

'Created by Peezy (Brandon Parrott)
'Used to Split full addresses in a single column into mutiple columns
'To use put =street(a1) into your street column

Dim str As String
    
    If InStr(rng.Value, "DR") > 0 Then 'Drive
        str = InStr(rng.Value, "DR") + 2
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "CIR") > 0 Then 'circle
        str = InStr(rng.Value, "CIR") + 3
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "ST") > 0 Then 'Street
        str = InStr(rng.Value, "ST") + 2
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "RD") > 0 Then 'Road
        str = InStr(rng.Value, "RD") + 2
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "AVE") > 0 Then 'Avenue
        str = InStr(rng.Value, "AVE") + 3
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "LN") > 0 Then 'Lane
        str = InStr(rng.Value, "LN") + 2
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "WAY") > 0 Then 'Way
        str = InStr(rng.Value, "WAY") + 3
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "RDG") > 0 Then 'Ridge
        str = InStr(rng.Value, "RDG") + 3
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "PKWY") > 0 Then 'Parkway
        str = InStr(rng.Value, "PKWY") + 4
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "HWY") > 0 Then 'Highway
        str = InStr(rng.Value, "HWY") + 3
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "TERR") > 0 Then 'Terrace
        str = InStr(rng.Value, "TERR") + 4
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "TER") > 0 Then 'Terrace
        str = InStr(rng.Value, "TER") + 3
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "BLVD") > 0 Then 'Boulavard
        str = InStr(rng.Value, "BLVD") + 4
            street = Left(rng, str)
    ElseIf InStr(rng.Value, "LOOP") > 0 Then 'Loop
        str = InStr(rng.Value, "Loop") + 4
            street = Left(rng.str)
    ElseIf InStr(rng.Value, "P O BOX") > 0 Then 'P.O. Box Address
        street = pobox(rng)
    ElseIf InStr(rng.Value, "PO BOX") > 0 Then 'PO Box address Incase there is no space between P and O
        street = pobox(rng)
    Else
        street = "Error"
    End If

End Function

Function pobox(rng As Range)

'Created by Peezy (Brandon Parrott)
'Used to Split full addresses in a single column into mutiple columns
'pobox function is used along with the street function, no need to mess with this in the sheet
'Determines if the address is a P.O. Box
 
Dim strr As String, result As String
Dim r As Integer, t As Integer

strr = rng

    If InStr(1, strr, "1") = 9 Then
        r = InStr(1, strr, "1")
            t = InStr(r, strr, " ")
                result = Left(strr, t)
    ElseIf InStr(1, strr, "2") = 9 Then
        r = InStr(1, strr, "2")
            t = InStr(r, strr, " ")
                result = Left(strr, t)
    ElseIf InStr(1, strr, "3") = 9 Then
        r = InStr(1, strr, "3")
            t = InStr(r, strr, " ")
                result = Left(strr, t)
    ElseIf InStr(1, strr, "4") = 9 Then
        r = InStr(1, strr, "4")
            t = InStr(r, strr, " ")
                result = Left(strr, t)
    ElseIf InStr(1, strr, "5") = 9 Then
        r = InStr(1, strr, "5")
            t = InStr(r, strr, " ")
                result = Left(strr, t)
    ElseIf InStr(1, strr, "6") = 9 Then
        r = InStr(1, strr, "6")
            t = InStr(r, strr, " ")
                result = Left(strr, t)
    ElseIf InStr(1, strr, "7") = 9 Then
        r = InStr(1, strr, "7")
            t = InStr(r, strr, " ")
                result = Left(strr, t)
    ElseIf InStr(1, strr, "8") = 9 Then
        r = InStr(1, strr, "8")
            t = InStr(r, strr, " ")
                result = Left(strr, t)
    ElseIf InStr(1, strr, "9") = 9 Then
        r = InStr(1, strr, "9")
            t = InStr(r, strr, " ")
                result = Left(strr, t)
    Else
        result = "Error"
    End If
    
pobox = result

End Function

=city(a2) Function:

Function city(rng As Range)

'Created by Peezy (Brandon Parrott)
'Used to Address City from full address
'To use place =city(a1) into city column

Dim x As String
Dim str As String
Dim p As String, o As String, l As String
    
    If InStr(rng.Value, "DR") > 0 Then 'Drive
        str = InStr(rng.Value, "DR") + 2
    ElseIf InStr(rng.Value, "CIR") > 0 Then 'Circle
        str = InStr(rng.Value, "CIR") + 3
    ElseIf InStr(rng.Value, "ST") > 0 Then 'Street
        str = InStr(rng.Value, "ST") + 2
    ElseIf InStr(rng.Value, "RD") > 0 Then 'Road
        str = InStr(rng.Value, "RD") + 2
    ElseIf InStr(rng.Value, "AVE") > 0 Then 'Avenue
        str = InStr(rng.Value, "AVE") + 3
    ElseIf InStr(rng.Value, "LN") > 0 Then 'Lane
        str = InStr(rng.Value, "LN") + 2
    ElseIf InStr(rng.Value, "WAY") > 0 Then 'Way
        str = InStr(rng.Value, "WAY") + 3
    ElseIf InStr(rng.Value, "RDG") > 0 Then 'Ridge
        str = InStr(rng.Value, "RDG") + 3
    ElseIf InStr(rng.Value, "PKWY") > 0 Then 'Parkway
        str = InStr(rng.Value, "PKWY") + 4
    ElseIf InStr(rng.Value, "HWY") > 0 Then 'Highway
        str = InStr(rng.Value, "HWY") + 3
    ElseIf InStr(rng.Value, "TERR") > 0 Then 'Terrace
        str = InStr(rng.Value, "TERR") + 4
    ElseIf InStr(rng.Value, "TER") > 0 Then 'Terrace
        str = InStr(rng.Value, "TER") + 3
    ElseIf InStr(rng.Value, "BLVD") > 0 Then 'boulevard
        str = InStr(rng.Value, "BLVD") + 4
    ElseIf InStr(rng.Value, "P O BOX") > 0 Then 'If address is a P O Box
        p = InStr(rng.Value, "BOX") + 4
        str = InStr(p, rng.Value, " ")
    ElseIf InStr(rng.Value, "PO BOX") > 0 Then 'If address is a PO Box
        p = InStr(rng.Value, "BOX") + 4
        str = InStr(p, rng.Value, " ")
    Else
        x = "Error"
    End If
    
x = Left(rng, str)

Dim r           As String
Dim s           As String
Dim v           As String
Dim reg         As String
Dim obj         As String
Dim result      As String
Dim a           As String

s = Right(rng, 10)

    If IsNumeric(s) = True Then
        v = 12
    ElseIf IsNumeric(s) = False Then
        v = 8
    End If

r = Len(rng.Value) - v
reg = Left(rng, r)
obj = Len(x)
a = r - obj
result = Right(reg, a)

city = result

End Function

=state(a2) Function:

Function state(rng As Range)

'Created by Peezy (Brandon Parrott)
'Used to get Address State
'To use use =State(A1) in your State Column

Dim r       As String
Dim s       As String
Dim v       As String

s = Right(rng, 10)

    If IsNumeric(s) = True Then
        v = 12
    ElseIf IsNumeric(s) = False Then
        v = 8
    End If

r = Trim(rng.Value)
r = Right(r, v)
r = Left(r, 2)

    Select Case r
        Case Is = "AL"
            state = "AL"
        Case Is = "AK"
            state = "AK"
        Case Is = "AZ"
            state = "AZ"
        Case Is = "AR"
            state = "AR"
        Case Is = "CA"
            state = "CA"
        Case Is = "CO"
            state = "CO"
        Case Is = "CT"
            state = "CT"
        Case Is = "DE"
            state = "DE"
        Case Is = "DC"
            state = "DC"
        Case Is = "FL"
            state = "FL"
        Case Is = "GA"
            state = "GA"
        Case Is = "HI"
            state = "HI"
        Case Is = "ID"
            state = "ID"
        Case Is = "IL"
            state = "IL"
        Case Is = "IN"
            state = "IN"
        Case Is = "IA"
            state = "IA"
        Case Is = "KS"
            state = "KS"
        Case Is = "KY"
            state = "KY"
        Case Is = "LA"
            state = "LA"
        Case Is = "ME"
            state = "ME"
        Case Is = "MD"
            state = "MD"
        Case Is = "MA"
            state = "MA"
        Case Is = "MI"
            state = "MI"
        Case Is = "MN"
            state = "MN"
        Case Is = "MS"
            state = "MS"
        Case Is = "MO"
            state = "MO"
        Case Is = "MT"
            state = "MT"
        Case Is = "NE"
            state = "NE"
        Case Is = "NV"
            state = "NV"
        Case Is = "NH"
            state = "NH"
        Case Is = "NJ"
            state = "NJ"
        Case Is = "NM"
            state = "NM"
        Case Is = "NY"
            state = "NY"
        Case Is = "NC"
            state = "NC"
        Case Is = "ND"
            state = "ND"
        Case Is = "OH"
            state = "OH"
        Case Is = "OK"
            state = "OK"
        Case Is = "OR"
            state = "OR"
        Case Is = "PA"
            state = "PA"
        Case Is = "RI"
            state = "RI"
        Case Is = "SC"
            state = "SC"
        Case Is = "SD"
            state = "SD"
        Case Is = "TN"
            state = "TN"
        Case Is = "TX"
            state = "TX"
        Case Is = "UT"
            state = "UT"
        Case Is = "VT"
            state = "VT"
        Case Is = "VI"
            state = "VI"
        Case Is = "VA"
            state = "VA"
        Case Is = "WA"
            state = "WA"
        Case Is = "WV"
            state = "WV"
        Case Is = "WI"
            state = "WI"
        Case Is = "WY"
            state = "WY"
        Case Else
            state = "Error"
    End Select

    
End Function

=zip(a2) Function:

Function zip(rng As Range)

'Created by Peezy (Brandon Parrott)
'Used to get the address Zip Code
'To use put =Zip(A1) into your Zip Code Column

Dim s As String
Dim v As String

s = Right(rng, 10)

If IsNumeric(s) = True Then
    v = Left(rng, Len(rng) - 4)
    v = Right(v, 5)
ElseIf IsNumeric(s) = False Then
    v = Right(rng, 5)
End If

zip = v

End Function

=zip4(a2) Function:

Function zip4(rng As Range)

'Created by Peezy (Brandon Parrott)
'Used if the address has a +4 Zip Code
'To use put =zip4(a1) into your +4 Zip Code Column

Dim s As String
Dim v As String
Dim r As String

s = Right(rng, 10)

If IsNumeric(s) = True Then
    r = Trim(s)
    v = Right(r, 4)
ElseIf IsNumeric(s) = False Then
End If

zip4 = v

End Function