I have a list of addresses, where I want to sort by road column, but ignore
the numbers in front of the road name. When I try to sort it sorts by the
house number.
e.g
1 Smith Road
3 John Road
4 Yellow Road

Split this to two columns. From menu ..Data..Text to Columns ..Fixed width
and separate the number from the text..

If this post helps click Yes
---------------
Jacob Skaria


"Al" wrote:

I have a list of addresses, where I want to sort by road column, but ignore
the numbers in front of the road name. When I try to sort it sorts by the
house number.
e.g
1 Smith Road
3 John Road
4 Yellow Road

thanks that's good to know but I need to keep the numbers together with the
road name - when I separate, and then sort the numbers are not aligned to the
correct road name...

"Al" wrote:

I have a list of addresses, where I want to sort by road column, but ignore
the numbers in front of the road name. When I try to sort it sorts by the
house number.
e.g
1 Smith Road
3 John Road
4 Yellow Road

Hi Al,

With your short sample in F1 to F3, in H1 enter and pull down this:

=MID(F1,FIND(" ",F1)+1,1)

You will have J, S, H in H1 to H3.

Select H1 through F1 and down to F3...(going backward here) and sort
Acending. Delete column H formulas.

I changed your sample to this and it works if the road number is greater
than a single digit.

333 John Road
1 Smith Road
12345 Yellow Road

You still get J, S, H in H1 to H3 to do your sort.

HTH
Regards,
Howard

"Al" wrote in message
...
I have a list of addresses, where I want to sort by road column, but ignore
the numbers in front of the road name. When I try to sort it sorts by the
house number.
e.g
1 Smith Road
3 John Road
4 Yellow Road