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

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

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

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

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

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