software

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--
BrettOlbrys
------------------------------------------------------------------------
BrettOlbrys's Profile: www.excelforum.com/member.php...foamp;userid=1786
View this thread: www.excelforum.com/showthread...hreadid=496583
Probably not the answer you want - but copy your sample to a Word
Document and it will sort as you want.
Sort Order in Word
901 RT BS
930V/C B
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
62062
62063
62093
63204 MC/FR
87000 BLK
89120--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: www.excelforum.com/member.php...oamp;userid=29640
View this thread: www.excelforum.com/showthread...hreadid=496583Are these imported data? I experienced similar problems with imported data,
e.g. converted from a .TXT file. If so, you should first transform them (e.g.
in a helper column) with the TEXT(cellreference,quot;@quot;) function then sort by
the transformed column (you can copy and pastespecial-values the transformed
column back to their original place, if necessary).

Regards,
Stefi?rettOlbrys??ezt ?rta:

gt;
gt; I have a column of data in random order and when I Sort the data by
gt; ascending order (I have hundreds of pages of these), the result is as
gt; below:
gt;
gt; 62062
gt; 62063
gt; 62093
gt; 89120
gt; 20294 SAT
gt; 20294 WH
gt; 20309 WH
gt; 20310 BLK
gt; 20310 WH
gt; 23109 COLBALT
gt; 25013 OS
gt; 50362 BS
gt; 50362 SB
gt; 50367 BS
gt; 50368 BS
gt; 50370 BS
gt; 50370 SB
gt; 50398 BS
gt; 52030 BS
gt; 52032 BS
gt; 52106 BS FR
gt; 52133 BS
gt; 52203 BS
gt; 63204 MC/FR
gt; 87000 BLK
gt; 901 RT BS
gt; 930V/C B
gt;
gt; I need for the data to be in numerical order (ascending) so that (for
gt; example) the 901 RT BS is at the top of the list b/c 901 is a smaller
gt; value then 930 and the 89120 would be the last b/c 89120 is the largest
gt; value number, etc... and I need that to occur even if ther is text at
gt; the end of the number.
gt;
gt; How do you tell Excel to do this?
gt;
gt; Thanks,
gt;
gt; Brett
gt;
gt;
gt; --
gt; BrettOlbrys
gt; ------------------------------------------------------------------------
gt; BrettOlbrys's Profile: www.excelforum.com/member.php...foamp;userid=1786
gt; View this thread: www.excelforum.com/showthread...hreadid=496583
gt;
gt;


It's not feasable to sort in Word b/c I have 200 pages like this to
sort. The data was not imported, it was typed directly into the
cells.

I simply want the data to ascend from lowest to highest just like the
Word example above, so how do you do in Excel when there is text in
there too?

Thanks,

Brett--
BrettOlbrys
------------------------------------------------------------------------
BrettOlbrys's Profile: www.excelforum.com/member.php...foamp;userid=1786
View this thread: www.excelforum.com/showthread...hreadid=496583Assume the values start in A1, in B1 put

=IF(ISERR(--A1),--LEFT(A1,MATCH(FALSE,ISNUMBER(--MID(A1,ROW(INDIRECT(quot;1:quot;amp;LEN(A1))),1)),0)-1),A1)

entered with ctrl shift amp; enter

(if column B or the adjacent column is not available insert a new column to
the right and put the formula there adapted to your real values)

copy down as long as needed then select both columns and sort by the help
column

--
Regards,

Peo Sjoblom

(No private emails please)quot;BrettOlbrysquot; gt;
wrote in message
...
gt;
gt; It's not feasable to sort in Word b/c I have 200 pages like this to
gt; sort. The data was not imported, it was typed directly into the
gt; cells.
gt;
gt; I simply want the data to ascend from lowest to highest just like the
gt; Word example above, so how do you do in Excel when there is text in
gt; there too?
gt;
gt; Thanks,
gt;
gt; Brett
gt;
gt;
gt; --
gt; BrettOlbrys
gt; ------------------------------------------------------------------------
gt; BrettOlbrys's Profile:
gt; www.excelforum.com/member.php...foamp;userid=1786
gt; View this thread: www.excelforum.com/showthread...hreadid=496583
gt;On Wed, 28 Dec 2005 22:46:19 -0600, BrettOlbrys
gt; wrote:

gt;
gt;I have a column of data in random order and when I Sort the data by
gt;ascending order (I have hundreds of pages of these), the result is as
gt;below:
gt;
gt;62062
gt;62063
gt;62093
gt;89120
gt;20294 SAT
gt;20294 WH
gt;20309 WH
gt;20310 BLK
gt;20310 WH
gt;23109 COLBALT
gt;25013 OS
gt;50362 BS
gt;50362 SB
gt;50367 BS
gt;50368 BS
gt;50370 BS
gt;50370 SB
gt;50398 BS
gt;52030 BS
gt;52032 BS
gt;52106 BS FR
gt;52133 BS
gt;52203 BS
gt;63204 MC/FR
gt;87000 BLK
gt;901 RT BS
gt;930V/C B
gt;
gt;I need for the data to be in numerical order (ascending) so that (for
gt;example) the 901 RT BS is at the top of the list b/c 901 is a smaller
gt;value then 930 and the 89120 would be the last b/c 89120 is the largest
gt;value number, etc... and I need that to occur even if ther is text at
gt;the end of the number.
gt;
gt;How do you tell Excel to do this?
gt;
gt;Thanks,
gt;
gt;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(quot;1:255quot;)),1)),0)-1)

This is an **array** formula, so after typing or pasting it into the formula
bar, hold down lt;ctrlgt;lt;shiftgt; while hitting lt;entergt;. 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


Works great. Thank you!

Brett--
BrettOlbrys
------------------------------------------------------------------------
BrettOlbrys's Profile: www.excelforum.com/member.php...foamp;userid=1786
View this thread: www.excelforum.com/showthread...hreadid=496583On Thu, 29 Dec 2005 23:58:43 -0600, BrettOlbrys
gt; wrote:

gt;
gt;Works great. Thank you!
gt;
gt;Brett

You're welcome. Glad to help.
--ron