On Wed, 28 Dec 2005 22:46:19 -0600, BrettOlbrys
wrote:


I have a column of data in random order and when I Sort the data by
ascending order (I have hundreds of pages of these), the result is as
below:

62062
62063
62093
89120
20294 SAT
20294 WH
20309 WH
20310 BLK
20310 WH
23109 COLBALT
25013 OS
50362 BS
50362 SB
50367 BS
50368 BS
50370 BS
50370 SB
50398 BS
52030 BS
52032 BS
52106 BS FR
52133 BS
52203 BS
63204 MC/FR
87000 BLK
901 RT BS
930V/C B

I need for the data to be in numerical order (ascending) so that (for
example) the 901 RT BS is at the top of the list b/c 901 is a smaller
value then 930 and the 89120 would be the last b/c 89120 is the largest
value number, etc... and I need that to occur even if ther is text at
the end of the number.

How do you tell Excel to do this?

Thanks,

Brett

Use a helper column which contains only the numeric portion, and sort on that.

For example:

With your data in A1:An

B1: =--LEFT(A1,MATCH(TRUE,ISERROR(-MID(A1,ROW(INDIRECT("1:255")),1)),0)-1)

This is an **array** formula, so after typing or pasting it into the formula
bar, hold down <ctrl<shift while hitting <enter. Excel will place braces
{...} around the formula.

Select B1 and copy/drag the formula down to Bn. This will extract the numeric
portion of the values in column A.

Select An:Bn and, from the main menu: Data/Sort Column B Ascending.




--ron