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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 05:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com