Split Data and Concatenate

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
 
T

Tom Ogilvy

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.
 
B

Bob Phillips

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)
 
T

Tom Ogilvy

Didn't work for the first example since no change was required. Might need
to add something to check for that.
 
A

Andrew Slentz

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
A

Andrew Slentz

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
T

Tom Ogilvy

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
 
Top