Still Split Data and Concatenate

[Andrew Slentz]

I have data in Excel such as:

A1 B1
Suite 1 Anytown
-OR-
A1 B1
Suite 1 Bldg 3, Anytown
-OR-
A1 B1
Suite 1 AnyBuilding, Lot 2, Anytown

I need to get everything to the left of the last comma into the A1
cell and leave only what is to the left of the rightmost comma in the
A2 cell. Make sense? I would like to do this using a macro for
simplicity. Any ideas???

A really nice, helpful person gave me the following but I, incorrectly,
told him the cells to be used were A1 & A2, they should be A1&B1. I
also need to know how to change the cells if needed.

Thanks again and again,

Andrew


Sub Tester1()

Dim i As Long, lastrow As Long
Dim j As Long, k As Long, iloc As Long
Dim sStr As String
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow Step 2
sStr = Cells(i + 1, 1).Value
j = Len(sStr)
iloc = 0
For k = j To 1 Step -1
If Mid(sStr, k, 1) = "," Then
iloc = k
Exit For
End If
Next
If iloc <> 0 Then
Cells(i, 1).Value = Cells(i, 1). _
Value & " " & _
Trim(Left(sStr, iloc - 1))
Cells(i + 1, 1).Value = Trim( _
Right(sStr, j - iloc))
End If
Next
End Sub

 

[Tom Ogilvy]

Sub Tester1()

Dim i As Long, lastrow As Long
Dim j As Long, k As Long, iloc As Long
Dim sStr As String
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow Step 1
sStr = Cells(i, 2).Value
j = Len(sStr)
iloc = 0
For k = j To 1 Step -1
If Mid(sStr, k, 1) = "," Then
iloc = k
Exit For
End If
Next
If iloc <> 0 Then
Cells(i, 1).Value = Cells(i, 1). _
Value & " " & _
Trim(Left(sStr, iloc - 1))
Cells(i, 2).Value = Trim( _
Right(sStr, j - iloc))
End If
Next
End Sub

Cells(i,1) refers to Column A
cells(i,2) refers to Column B