The following macro is one way to do this. It assumes that everything up to
the first <space> is the street number (and should be kept in the current
column), and everything after that <space> should be moved to the adjacent
column. It currently acts on Column A; if your addresses are in a different
column, just change "A:A" to the appropriate column (e.g., if they're in
Column C, change it to "C:C"). As I said, it also assumes that you want to
use the column immediately to the right for the street portion, so make sure
you have a blank column there.
Note also that the macro stops as soon as it comes to a blank line in the
target column. One other thing: it works on whatever is the currently active
worksheet, so you'll of course want to assure you have the correct sheet
showing.
Here's the macro:
' macro follows
Sub SplitAddresses()
Dim s As String
Dim i As Integer
Dim cell As Range
For Each cell In Range("A:A").Cells
s = cell.Value
If s = "" Then Exit Sub
i = InStr(s, " ")
If i > 0 Then
cell.Offset(0, 1).Value = Mid(s, i + 1)
cell.Value = Left(s, i - 1)
End If
Next
End Sub
' that's it...end of macro
If you already know how to enter a macro, you can skip the next bit.
Otherwise, on the Tools menu, go to Macro then select Visual Basic Editor
(the VBE). In the VBE, select Module from the Insert menu. Then just paste
in the above code. Return to Excel and select Run Macro and choose
'SplitAddresses'.
George
Hello,
I've got a worksheet with address data in it. I'd like to put house
#'s and street numbers in seperate columns.
For example;
"2304 41st Ave E" would become "2304" "41st Ave E"
Can anybody help me figure out how to do this?
Thanks
John
--
Mac Word FAQ: <
http://www.mvps.org/word/FAQs/WordMac/index.html>
Entourage Help Page: <
http://www.entourage.mvps.org/>
Or try Google: <
http://www.google.com>
Please do not reply to this message. Although the email address is perfectly
valid, any replies to this account never get to the Inbox on my computer.
