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