Sorting data bust ignoring numbers

[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

 

[Jacob Skaria]

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

 

[Al]

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

 

[L. Howard Kittle]

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