Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I sort postal addresses by street name, then number
The street address column has street number and name together. (28
Maple Ave.) How do I sort by street name, then by number? thanks for any help |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I sort postal addresses by street name, then number
Split into 2 columns
-- Hth Kassie Kasselman Change xxx to hotmail "Mel" wrote: The street address column has street number and name together. (28 Maple Ave.) How do I sort by street name, then by number? thanks for any help |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I sort postal addresses by street name, then number
Yes the best way would be to separate the first part of the string
You could use text to columns Data=text to columns Or use this =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) This will display anything after the first space in the string Then you can sort |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I sort postal addresses by street name, then number
Hi Mel
I would add another column at the right of existing data. Assuming your address is in column A, enter in the new column =MID(A2,FIND(" ",A2)+1)&" "&TEXT(LEFT(A2,FIND(" ",A2)-1),"0000") This will reverse the data Maple Avenue 0028 Then Mark your whole block of data but sort on the new Column You will then Have the data sorted by Street, and by number within Street (as long as numbers are less than 9999) -- Regards Roger Govier "Mel" wrote in message ... The street address column has street number and name together. (28 Maple Ave.) How do I sort by street name, then by number? thanks for any help |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I sort postal addresses by street name, then number
Apologies Mel
I left out part of the mid function. That should have read =MID(A2,FIND(" ",A2)+1,99)&" "&TEXT(LEFT(A2,FIND(" ",A2)-1),"0000") -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Mel I would add another column at the right of existing data. Assuming your address is in column A, enter in the new column =MID(A2,FIND(" ",A2)+1)&" "&TEXT(LEFT(A2,FIND(" ",A2)-1),"0000") This will reverse the data Maple Avenue 0028 Then Mark your whole block of data but sort on the new Column You will then Have the data sorted by Street, and by number within Street (as long as numbers are less than 9999) -- Regards Roger Govier "Mel" wrote in message ... The street address column has street number and name together. (28 Maple Ave.) How do I sort by street name, then by number? thanks for any help |
#6
Posted to microsoft.public.excel.setup
|
|||
|
|||
How do I sort postal addresses by street name, then number
Thanks, it worked well.
On Feb 8, 5:37*pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Apologies Mel I left out part of the mid function. That should have read =MID(A2,FIND(" ",A2)+1,99)&" "&TEXT(LEFT(A2,FIND(" ",A2)-1),"0000") -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Mel I would add another column at the right of existing data. Assuming your address is in column A, enter in the new column =MID(A2,FIND(" ",A2)+1)&" "&TEXT(LEFT(A2,FIND(" ",A2)-1),"0000") This will reverse the data Thanks much. It worked well. Maple Avenue 0028 Then Mark your whole block of data but sort on the new Column You will then Have the data sorted by Street, and by number within Street (as long as numbers are less than 9999) -- Regards Roger Govier "Mel" wrote in message .... The street address column has street number and name together. (28 Maple Ave.) *How do I sort by street name, then by number? thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transposing postal addresses | Excel Worksheet Functions | |||
How do I sorty by addresses, ignoring number, using street name | Excel Worksheet Functions | |||
Sorting street addresses | Excel Discussion (Misc queries) | |||
Help! How to sort addresses by street then by #...... | New Users to Excel | |||
How do I sort a column of street number/street name by the stree. | Excel Worksheet Functions |