Sorting with letters and numbers

B

beefycj5

I'm trying to sort combinations of letters and numbers in a "real world
order. I cannot efficiently use helper columns to extract the letter
into one column and the numbers into another, because I've got 20,00
records, some with one letter, some with two letters, some with 3
etc.

Example:

Excel sort:
AS1
AS100
AS2
ATS1
ATS100
ATS2

Real world sort:
AS1
AS2
AS100
ATS1
ATS2
ATS100

Keep in mind that I've got 20,000 records with letter amounts tha
vary. I can't figure out a way to use helper columns efficiently
because Excel won't sort the column by the number of letters.

Thanks for the help
 
J

JulieD

Hi

there might be an easier way, but this seems to work ... how about two
helper columns - one to extract the text (use this as the first sort by) and
one to extract the numbers (use this as the second sort by field)

to extract numbers - use the following array formula (originally posted by
Frank Kabel) - place in column B with your data in column A (and remember to
enter with CONTROL & SHIFT & ENTER)
=--MID(A1,MIN(IF(ISNUMBER(-MID(A1,seq,1)),seq)),LOOKUP(2,1/ISNUMBER(-MID(A1,seq,1)),seq)-MIN(IF(ISNUMBER(-MID(A1,seq,1)),seq))+1)
where seq is a defined name with the formula
seq: =ROW(INDIRECT("1:1024"))

to extract the text (place in column C)
=LEFT(A1,LEN(A1)-LEN(B1))

fill down as necessary and then sort using column C as first sort order &
column B as second.

Hope this helps
Cheers
JulieD
 
R

RagDyeR

There *is* a way to do it using "helper" columns, as long as your data is
similar to what you posted.
This formula works with *one* set of numbers and *one* set of letters.

With your data in Column A, enter this *array* formula in B1:

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

This will extract the numbers.


Enter this formula in C1 to extract the letteers:
=SUBSTITUTE(A1,B1,"")

NOW, select *both* B1 and C1, and drag down to copy, as needed.
*OR*
You can *double* click the "fill handle" of the selection of B1 & C1, which
will *automatically* copy both formulas down columns B & C as far as there
is data in Column A.

Now, all you have to do is select all 3 columns and sort on C, then B, and A
goes along for the ride, and comes out as you wish.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I'm trying to sort combinations of letters and numbers in a "real world"
order. I cannot efficiently use helper columns to extract the letters
into one column and the numbers into another, because I've got 20,000
records, some with one letter, some with two letters, some with 3,
etc.

Example:

Excel sort:
AS1
AS100
AS2
ATS1
ATS100
ATS2

Real world sort:
AS1
AS2
AS100
ATS1
ATS2
ATS100

Keep in mind that I've got 20,000 records with letter amounts that
vary. I can't figure out a way to use helper columns efficiently,
because Excel won't sort the column by the number of letters.

Thanks for the help.
 
R

RagDyeR

BTW - I copied my formula from PEO.

So who was the originator ?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

There *is* a way to do it using "helper" columns, as long as your data is
similar to what you posted.
This formula works with *one* set of numbers and *one* set of letters.

With your data in Column A, enter this *array* formula in B1:

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

This will extract the numbers.


Enter this formula in C1 to extract the letteers:
=SUBSTITUTE(A1,B1,"")

NOW, select *both* B1 and C1, and drag down to copy, as needed.
*OR*
You can *double* click the "fill handle" of the selection of B1 & C1, which
will *automatically* copy both formulas down columns B & C as far as there
is data in Column A.

Now, all you have to do is select all 3 columns and sort on C, then B, and A
goes along for the ride, and comes out as you wish.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I'm trying to sort combinations of letters and numbers in a "real world"
order. I cannot efficiently use helper columns to extract the letters
into one column and the numbers into another, because I've got 20,000
records, some with one letter, some with two letters, some with 3,
etc.

Example:

Excel sort:
AS1
AS100
AS2
ATS1
ATS100
ATS2

Real world sort:
AS1
AS2
AS100
ATS1
ATS2
ATS100

Keep in mind that I've got 20,000 records with letter amounts that
vary. I can't figure out a way to use helper columns efficiently,
because Excel won't sort the column by the number of letters.

Thanks for the help.
 
D

David McRitchie

Hi ____,
Sorry didn't look to see that you already had answers and an
accepted solution. Consider this another solution.

So how does 20k rows prevent you from using a helper column.
See http://www.mvps.org/dmcritchie/excel/fillhand.htm
use the name box to make your huge selection then Ctrl+D for Fill Down
If there is content to the left, simply double click the fill handle, and then
check at the bottom of what you want to make sure it filled down that far.

As far as extracting letters in one column and digits in another
column see
Sorting product code with alpha prefix and numeric suffix (#pcdigits)
http://www.mvps.org/dmcritchie/excel/sorting.htm#pcdigits

Helper Column 1 & 2 derived from a posting by Tom Ogilvy in 1999.
D2:
=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)
It is an array formula, so enter it with Ctrl+shift+enter

E2: =RIGHT(A2,LEN(A2)-LEN(D2))*1


the above will work for your data, but here are a couple more pages.
Sorting TCP/IP Addresses, and the like
http://www.mvps.org/dmcritchie/excel/sorttcp.htm
and for some really neat stuff with Regular Expressions
Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm

Help provided from the Excel newsgroups.
 
Top