Sorting data bust ignoring numbers
 

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

Sorting data bust ignoring numbers
 

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

Sorting data bust ignoring numbers
 

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

Sorting data bust ignoring numbers
 

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