Ian, you could write a macro to either break each cell into two cells (automate
the work of re-editing the column into two columns) or write a macro to do the
entire sort, ignoring the first token in this column. I'd recommend the first.
I don't have much practice in writing Excel macros, but this is what I came up
with:
Public Sub intoTwo()
Dim row As Variant
Dim newRow As Variant
For Each row In Selection.Rows
newRow = Split(row.Cells(1, 2), " ", 2)
row.Cells(1, 1) = newRow(0)
row.Cells(1, 2) = newRow(1)
Next
End Sub
I expect that this can be written much better, and would appreciate anyone's
improvements. You have to manually insert a new column to the left of the
existing address column and then select both columns. (When I tested, I didn't
literally select the columns, I selected the cells.)
On Wed, 6 Aug 2003 10:39:07 -0400, Ian Carmichael <[log in to unmask]> wrote:
>Hello all. Need some help with the following; note that I am not a heavy MS
>Office user.
>
>I recently started receiving work-orders via spreadsheet. Data consists of
>acct. numbers, names, addresses and phone info. I need info first sorted by
>zipcode then by address. It is in the addressing fields that I'm having
>problems.
>
>For example, say I have the following addresses
>
>10 E. 101 st
>40 Garrett St
>123 Main St
>100 E. 105 St
>3005 E. 83 St
>111 Willis St
>1400 Smith St
>3005 E. 83 St
>
>After a sort, I end up with the following order:
>
>10 E. 101 st
>100 E. 105 St
>1001 E. 103 St
>111 Willis St
>123 Main St
>1400 Smith St
>3005 E. 83 St
>40 Garrett St.
>
>I need to keep the "E."(East) street addresses in contiguous order, but
>can't seem to do so. I realize it's sorting on the house numbers. Is
>there any way of sorting the way I want it without re-editing multiple
>pages of document(s). Any help greatly appreciated. TIA
*-*-*-*-*-*
Harry
The NOSPIN Group is now offering Free PC Tech
support at our newest website:
http://freepctech.com
|