Sorting data bust ignoring numbers

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

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
 
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...
 
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
 
Back
Top