How can i sort a database on numbers?

S

Sylvester

I have a database with adresses of people. I have to print the database in
order of housenumber. The streetname and housenumber are in the same cell. If
i sort my database on the adress, i get the numbers starting with 1 first.
Like 1, 10, 11, 2, 22, 3, 37. How can i sort the right way?

Sylvester
 
G

Gord Dibben

Sylvester

Having the number and address in the same cell produces text formatted data.

The sort order is correct for this format.

The best way to get around this is to break out the number and address into two
columns and sort on the column with numbers.

Make sure you select all columns before sorting.


Gord Dibben MS Excel MVP
 
S

Sylvester

Thank you Gord, but is there an easy way to break up de data? If i use "text
to collums" i can't split text from numbers. I tried using the space as
separation but all i get is a a lot of cells with a piece of the adress.
Sometime a streetname has more then one word and one number. And sometimes
the housenumber has an housenumberextension (like 35a).

Regards,

Sylvester
 
G

Gord Dibben

Sounds like a nightmare.

Copy the column so's you have two columns the same.

To break out just the numbers try Data>Text to Columns>Delimited by>Space>Next.

Select columns other than the numbers column and "do not import".

The only column returned is the number.

Sort on that.

I don't know what you will do with the 35a's

David McRitchie has written macros for parsing street addresses.

http://www.mvps.org/dmcritchie/excel/join.htm#septerm


Gord
 
Top