Sorting Street Addresses by Street Name

[Guest]

I would like to sort a column that has Street Addresses including the number

It would be desirable to group the street name together and sort the number after the street name

ie 123 Main S
234 Aston Lan

should sort t

234 Aston Lan
123 Main S

Any Ideas

 

[Frank Kabel]

Hi
one way:
- split your data in two columns or use a helper column for the street
name.
- for the latter one enter the following in the first cell
=MID(A1,FIND(" ",A1)+1,255)
- copy down
sort with this helper column

 

[Bob Phillips]

Scott,

add a helper column and sort by that

B1: =RIGHT(A1,LEN(A1)- FIND(" ",A1)+1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Frank,

Thanks a million. Works great.

I'll have to study it a bit so I understand how...but my problem has been solved!


----- Frank Kabel wrote: -----

Hi
one way:
- split your data in two columns or use a helper column for the street
name.
- for the latter one enter the following in the first cell
=MID(A1,FIND(" ",A1)+1,255)
- copy down
sort with this helper column

 

[Frank Kabel]

Hi Scott
to give you some more information about the formula:
=MID(A1,FIND(" ",A1)+1,255)
- MID has the syntax MID(text,start,lenght)
so in this case it gets a sub-string from A1 starting at FIND(..)+1
with a lenght of max 255

- FIND(" ",A1) searches for the first space and returns the position
within A1