Parsing out text

Rysn

Try this macro

Replace the red a with the column letter that has the text to be parsed.

The macro places the address in the next column to the right of the text to be parsed - eg list in column A, Address will be placed in column B

I have also added 2 other options in the code
Option 1
replace name, address, company entry with name, company
Option 2
Place name, company details in the 2nd column to the right of the list

To enable 1 or both of these option remove the ' from the front of the lines of code in blue

Sub ParseText()
   Dim iPos(2) As Integer
   Dim iLastRow As Integer
   Dim Rng As Range
   Dim sTxt(3) As String
   
   iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
   For Each Rng In Range("a1:a" & iLastRow)
      iPos(0) = InStr(1, Rng.Value, " CH ")
      If iPos(0) > 0 Then
         iPos(1) = InStrRev(Rng.Value, ", ")
         ' get name & ch number
         sTxt(0) = Left(Rng.Value, iPos(0) + 8)
         'get address
         sTxt(1) = Mid(Rng.Value, iPos(0) + 9, (iPos(1) + 10) - (iPos(0) + 9))
         'get copany name
         sTxt(2) = Mid(Rng.Value, iPos(1) + 11)
         'place address in next column
         Rng.Offset(0, 1).Value = sTxt(1)
         'replace name, address, company with name, company
         'Rng.Value = sTxt(0) & sTxt(3)
         'place name & company in anothe rcolumn
         'Rng.Offset(0, 2).Value = sTxt(0) & sTxt(3)
      End If
   Next Rng

It is caused by the cmpany name having a comma in it.

The macro is programmed to look for the last comma and assumes that it is the devider between the zip code and the company name.

If the state abbreviation of IL is the only one listed in the data to be parsed then I can change the macro to look for IL instead of the last comma.

Another option is that if there are always only 2 comma's after the CH then I could also change the macro to work off the 2nd comma after the CH