Sorting Help
 

I am trying to sort data in a specific manner. My column shows
1
2
3
4
5
310
3A
3B

When sorted I want it to show
1
2
3
310
3A
3B
4
5

How can I get it to sort starting with the left most character and work
to the right and then down? :confused:


--
chainsaw
------------------------------------------------------------------------
chainsaw's Profile: http://www.excelforum.com/member.php...fo&userid=5976
View this thread: http://www.excelforum.com/showthread...hreadid=314426

You have a problem here, because some of the data is probably stored as
numbers, those entries with letters are stored as text. The only way that I
can think of to force the sort order you want is to convert all of the data to
text with the same number of characters.

Assuming that the maximum length is 3, you would have to add leading spaces to
numbers that are less than 3 digits; you would have to convert the 3-digit
numbers to text by adding a leading apostrophe; text entries like 3A and 3B
would have to be padded with a trailing space.

Or,for the data you have shown, you could use a couple of helper columns, one
containing the 1st digit of the number, the 2nd containing the rest of the
number. The formulas would be =LEFT(A1) and =MID(A1,2,255) Sorting on those
two columns would give the order you show. BUT... if you have entries like
93A, it won't work. That would be split into 9 and 3A and will sort with the 9
entries, not with the 93's.



On Wed, 17 Nov 2004 10:27:47 -0600, chainsaw
wrote:


I am trying to sort data in a specific manner. My column shows
1
2
3
4
5
310
3A
3B

When sorted I want it to show
1
2
3
310
3A
3B
4
5

How can I get it to sort starting with the left most character and work
to the right and then down? :confused:

One work-around would be to change the value of the data into text.

If the data is in column A, then in column B add the following formula(s)

in cell b1 =TEXT(A1,0)
in cell b2 =TEXT(A2,0)
....

Sort by column B

"chainsaw" wrote:


I am trying to sort data in a specific manner. My column shows
1
2
3
4
5
310
3A
3B

When sorted I want it to show
1
2
3
310
3A
3B
4
5

How can I get it to sort starting with the left most character and work
to the right and then down? :confused:


--
chainsaw
------------------------------------------------------------------------
chainsaw's Profile: http://www.excelforum.com/member.php...fo&userid=5976
View this thread: http://www.excelforum.com/showthread...hreadid=314426

Chainsaw,

as I see it you are looking for an alphabetic sort of data that can be
numeric. Obviously, 3A would be treated by Excel as text, whereas, 3, would
generally be treated as a number.

The simplest method to achieve your sort is to format the column containing
the references as text. Format/Cells/Number and select text as the format,
then try your sort. My memory of this is that in Excel 97 and prior, that it
would sort 11 after 1 and before 2, having just tried this in Excel XP, that
is no longer the case.

Steve
"chainsaw" wrote in message
...

I am trying to sort data in a specific manner. My column shows
1
2
3
4
5
310
3A
3B

When sorted I want it to show
1
2
3
310
3A
3B
4
5

How can I get it to sort starting with the left most character and work
to the right and then down? :confused:


--
chainsaw
------------------------------------------------------------------------
chainsaw's Profile:
http://www.excelforum.com/member.php...fo&userid=5976
View this thread: http://www.excelforum.com/showthread...hreadid=314426

You could use a single helper column and leave your
original column alone.

=Left(A1) & "-" & mid(A1,2,300)

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"chainsaw" wrote in message ...

I am trying to sort data in a specific manner. My column shows
1
2
3
4
5
310
3A
3B

When sorted I want it to show
1
2
3
310
3A
3B
4
5

How can I get it to sort starting with the left most character and work
to the right and then down? :confused:


--
chainsaw
------------------------------------------------------------------------
chainsaw's Profile: http://www.excelforum.com/member.php...fo&userid=5976
View this thread: http://www.excelforum.com/showthread...hreadid=314426