Split Data and Concatenate

  • Thread starter Thread starter Andrew Slentz
  • Start date Start date
A

Andrew Slentz

I have data in Excel such as:

A1 A2
Suite 1 Anytown
-OR-
A1 A2
Suite 1 Bldg 3, Anytown
-OR-
A1 A2
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???

Thanks in advance,

Andrew
 
This will work in just about any version of xl since xl5, at least in xl97
and later

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

You could us instrRev or split perhaps, but these were not introduced until
xl2000, so they would be restrictive.
 
Andrew,

You can do it with worksheet formulae

B1: =A1&",
"&LEFT(A2,FIND("~",SUBSTITUTE(A2,",","~",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))
)-1)
B2:
=MID(A2,FIND("~",SUBSTITUTE(A2,",","~",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))+
1,99)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Didn't work for the first example since no change was required. Might need
to add something to check for that.
 
I think I made a mistake in explaining this... I really appreciate the
help but I need to find out how to make it so that the data is in A1&B1
instead of A1&A2. I also need to know how to change those colums and
make this work for an entire spreadsheet.

Thanks so much for the help!

Andrew
 
OOPS!!! What you provided me was great, THANKS!!! I made a mistake and
should have said that instead of A1 and A2 I am using A1 & B2,
respectively. How do I change it to check those colums and work for all
rows of a spreadsheet???

Thanks a lot!!!

Andrew
 
Assuming A1 and B1

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) is column A
cells(i,2) is column B
 
Back
Top