How to Parse Street, City, State, and Zip Code into Separate Values. In Textbox

[jarekmos]

Hi, so I am a beginner to VBA and I got the code how to separate state, city, and zip code, but I don't know the code for how to do the street? Here's the code on how to separate city, state and zip code, and an image to show how I want to be separated in the textbox. I would appreciate if anyone can find the solution.

      Function CutLastWord (ByVal S As String, Remainder As String) _
         As String
      ' CutLastWord: returns the last word in S.
      ' Remainder: returns the rest.
      '
      ' Words are separated by spaces
      '
      Dim  I As Integer, P As Integer
        S = Trim$(S)
        P = 1
        For I = Len(S) To 1 Step -1
          If Mid$(S, I, 1) = " " Then
            P = I + 1
            Exit For
          End If
        Next I
        If P = 1 Then
          CutLastWord = S
          Remainder = ""
        Else
          CutLastWord = Mid$(S, P)
          Remainder = Trim$(Left$(S, P - 1))
        End If
      End Function

      Sub ParseCSZ (ByVal S As String, City As String, State As String, _
                    Zip As String)
      Dim P As Integer
      '
      ' Check for comma after city name
      '
        P = InStr(S, ",")
        If P > 0 Then
          City = Trim$(Left$(S, P - 1))
          S = Trim$(Mid$(S, P + 1))
      '
      '   Check for comma after state
      '
          P = InStr(S, ",")
          If P > 0 Then
            State = Trim$(Left$(S, P - 1))
            Zip = Trim$(Mid$(S, P + 1))
          Else                      ' No comma between state and zip
            Zip = CutLastWord(S, S)
            State = S
          End If
        Else                        ' No commas between city, state, or zip
          Zip = CutLastWord(S, S)
          State = CutLastWord(S, S)
          City = S
        End If
      '
      ' Clean up any dangling commas
      '
        If Right$(State, 1) = "," Then
          State = RTrim$(Left$(State, Len(State) - 1))
        End If
        If Right$(City, 1) = "," Then
          City = RTrim$(Left$(City, Len(City) - 1))
        End If
      End Sub

To test, create a form with four text boxes (txtAddress, txtCity, txtState, txtZip), and a command button. Add the following code:

 Sub Command1_Click()
      Dim City As String, State As String, Zip As String
        ParseCSZ txtAddress, City, State, Zip
        txtCity = City
        txtState = State
        txtZip = Zip
      End Sub

 

[FDibbins]

If this is in a text box, why not split it out in the TB to begin with?

 

[jarekmos]

Sorry, English is my second language and i don't really understand what you're trying to say. What do you mean about splitting it out in TB to begin with, im just trying to figure out how to edit this code to get the street in the 4th text box, so the fourth text box could be named street and the split will go in the text box not the cell, sorry if this isn't correctly worded because im not an excel expert. Thank you for trying to help.

Jarek