# Thread: Sorting (Excel 2000 )

1. ## Sorting (Excel 2000 )

Hi Guru's,

Hopefully you can help me........If I have one column with address for example 10 lyons St.......
how do I sort the contents by street name, not including number. The number keeps interfering.

Thanks Kindly,

Kerrie <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

2. ## Re: Sorting (Excel 2000 )

I think you're going to have to parse the column of data. Also, what happens when you have 1 Lyons Street and 2 Lyons Street? How do you want them sorted?

3. ## Re: Sorting (Excel 2000 )

You will either need to put the number and street name in separate columns, or create an additional column (possibly hidden) that contains the street name without the number. The latter can be done using a formula if you can gurantee that every street adderss will have a number. If not, you will need to do it manually, or write a User Defined Function.

4. ## Re: Sorting (Excel 2000 )

Kerrie,

This solution will depend on how consistent your data is. I have allowed for some cells to have street numbers and some not, and also I have tried to cater for inconsistent blanks in the data.

2. In the first column to the right enter the formula "=TRIM(A5)" where the address is in A5, and without the apostrophes. This will get rid of unwanted blanks in the address, leaving one blank between words.

3. In the second column enter the formula:

"=IF(LEFT(B5,1)<"A",RIGHT(B5,LEN(B5)-FIND(" ",B5)),B5)"

without the apostrophes. This checks if the first character has a lower value than "A" (a number) , and if so removes the whole number, otherwise it copies the whole cell.

You should now be able to sort on the right hand column entered.

I hope this helps.

Good Luck!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•