sorting a column with nums and text alphabetically

[lavaghman]

Hi,
have a excel sheet with a column that contains address details like:
22 Fitzwilliam St Upper
1 Kildare St
54-57 Lower Mount Street
2 Merrion Square
62/64 South William St
38/39 Pearse Street
Sean MacBride House

what i want to do is sort the street names alphabetically. If i perfor
a sort the way it is now i just get all the 1s first then 2s etc but
want the street that starts with A first.

What I was hoping for was a sort that would ignore numeric data an
sort alphabetically on the text

Any Suggestions???

Thanks for the help
Regards, John Mac
:confused

 

[CLR]

Hi John Mac

One way would be, assuming your data is in column A, put this in column B,
copied down, then copy > paste-speical > values on column B to get rid of
the formulas and then sort on column B

=MID(A1,FIND(" ",A1)+1,99)

Vaya con Dios,
Chuck, CABGx3

 

[Domenic]

Hi,

One way would be to use a helper column. Assuming that your data is i
Column A, put this formula in B1 and copy down:

=IF(ISNUMBER(--LEFT(A1,1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1)

Then, select the new column, copy > paste special > values, and sor
your data in Columns A and B by Column B.

Note that this won't work with an address such as:

4H Club

where there's no street number and no street address, like the one i
your example (Sean MacBride House).

Hope this helps!

 

[Max]

This might work ..

Assuming the sample addresses posted
are in A2 to A8:

22 Fitzwilliam St Upper
1 Kildare St
54-57 Lower Mount Street
2 Merrion Square
62/64 South William St
38/39 Pearse Street
Sean MacBride House

Put in B2:

=IF(ISNUMBER(LEFT(TRIM(A2),1)+0),MID(TRIM(A2),SEARCH("
",TRIM(A2))+1,99),TRIM(A2))

Copy B2 down to B8

This would remove the numbers
from the addresses (if any), giving:

Kildare St
Merrion Square
Fitzwilliam St Upper
Pearse Street
Lower Mount Street
South William St
Sean MacBride House

i.e. giving col B for use as the sort key col

Now you can sort both cols A and B by *col B* in ascending order

 

[lavaghman]

Thanks for your detailed replys Domenic, CLR & Max.....your help is muc
appreciated and has saved me alot of time trawling through endles
records! I tried all the suggestions but stuck with Domenic's in th
end.
Thanks again

Regards, John Ma

 

[Max]

You're welcome, John !
Thanks for the feedback