Sorting help needed!

[BrettOlbrys]

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

[wjohnson]

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

[Stefi]

Are 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,"@") 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


„BrettOlbrys” ezt �*rta:

>
> 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: http://www.excelforum.com/member.php...fo&userid=1786
> View this thread: http://www.excelforum.com/showthread...hreadid=496583
>
>

[BrettOlbrys]

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

[Peo Sjoblom]

Assume the values start in A1, in B1 put

=IF(ISERR(--A1),--LEFT(A1,MATCH(FALSE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1),A1)

entered with ctrl + shift & 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)


"BrettOlbrys" <[email protected]>
wrote in message
news:[email protected]...
>
> 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:
> http://www.excelforum.com/member.php...fo&userid=1786
> View this thread: http://www.excelforum.com/showthread...hreadid=496583
>

[Ron Rosenfeld]

On Wed, 28 Dec 2005 22:46:19 -0600, BrettOlbrys
<[email protected]> 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

[BrettOlbrys]

Works great. Thank you!

Brett

[Ron Rosenfeld]

On Thu, 29 Dec 2005 23:58:43 -0600, BrettOlbrys
<[email protected]> wrote:

>
>Works great. Thank you!
>
>Brett

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