CODEWITHSUNDEEP
excelcell
sbell423
sbell423

Reputation: 113

How to add a comma delimiter to the beginning of the city in an address

Example:

1412 Chestnut Street Philadelphia
494 W Germantown Pike Plymouth Meeting

I would like to add a comma to the beginning of each city in a list of 200 but as you can see above sometimes the city name does not start at the last word in the cell.

Is there an overarching formula that could add a comma before Philadelphia and Plymouth Meeting?

Upvotes: 0

Views: 1071

Answers (1)

Gary's Student
Gary's Student

Reputation: 96773

Here is a simple example that you can adapt to your use. Say We have addresses in column A and the city list in column B:

enter image description here

The following macro scans the addresses looking for a [space][city] and replaces the city with [,][city]

Sub Commafication()
   Dim Acol As String, Ccol As String
   Dim ia As Long, ic As Long, va As String, vc As String

''''''''''''''''''''''''''''''''''''
'  update these as needed
Acol = "A"
Ccol = "B"
iamax = 3
icmax = 6
''''''''''''''''''''''''''''''''''''

   For ia = 1 To iamax
      va = Cells(ia, Acol).Text
      For ic = 1 To icmax
         vc = Cells(ic, Ccol).Text
         If InStr(va, " " & vc) > 0 Then
            Cells(ia, Acol).Value = Replace(va, vc, "," & vc)
         End If
      Next ic
   Next ia
End Sub

Here is the result:

enter image description here

NOTE

This places the comma before the city name, as you requested, rather than placing a space between the comma and the city.

Upvotes: 1

Related Questions