How to Sort and Address

K

krystal123

I'm new to Excel and could use any help you are willing to share. I hav
a 300 page wortkbook, one column is address meaning street number an
name and a simple sort will not sort them. Is there a way to sort s
that I have all houses on the same street are together along wit
numbers in order as well????
 
J

joeu2004

krystal123 said:
I'm new to Excel and could use any help you are willing
to share. I have a 300 page wortkbook, one column is
address meaning street number and name [....]
Is there a way to sort so that I have all houses on the
same street are together along with numbers in order as
well?????

Assuming you have street number before street name, I think the easiest
thing to do is create a couple helper columns. The alternative is to use
VBA, which I would not advise for someone "new to Excel".

I assume that you want street number in numerical order, e.g. 11, 12, 13,
111, 121, 131, etc.

In that case, if your data are in A1:A300, put the following formula into B1
and C1, and copy B1:C1 down through B300:C300.

B1: =--LEFT(A1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,999)

The use "--" before LEFT converts the numeric text into a number.

The use of 999 with MID is arbitrary; just a big number to ensure that we
get the entire length of the string.

The select A1:C300 and use Sort, sorting first by column C, then by column
B.

Once you are done, you delete or high columns B and C.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top