Sorting number combinations

[STRAC]

Is there any way to sort numbers in a spreadsheet column that match a
specific criteria (NNNNNNANNNNNN). I have a 8500 record catalog with many
different types of stock numbers (incorrect) that I need to identify in order
to correct them (13 character, alpha/numeric, with the alpha in the 7th
position).

 

[David Biddulph]

=AND(LEN(A1)=13,ISNUMBER(--LEFT(A1,6)),ISNUMBER(--RIGHT(A1,6)),AND(UPPER(MID(A1,7,1))>="A",UPPER(MID(A1,7,1)<="Z")))
will return TRUE or FALSE for compliance with your requirement. Sort or
filter as appropriate.

 

[David Biddulph]

If A1 is where your data sits (or if you changed A1 in the formula to point
at where the first item of your data is), then that means that none of your
data meets your spec.

You can split the formula up to see which part fails:
=LEN(A1)=13 checks for a 13 character string
=ISNUMBER(--LEFT(A1,6)) checks for the left-hand 6 characters being numeric
=ISNUMBER(--RIGHT(A1,6)) checks for the right-hand 6 characters being
numeric
=AND(UPPER(MID(A1,7,1))>="A",UPPER(MID(A1,7,1)<="Z")) checks for the 7th
character being alphabetic

 

[Greg Wilson]

David, I think you have an error. The last UPPER included the <="Z" within
its parentheses: UPPER(MID(A1,7,1)<="Z")

Corrected formula:
=AND(LEN(A1)=13,ISNUMBER(--LEFT(A1,6)),ISNUMBER(--RIGHT(A1,6)),AND(UPPER(MID(A1,7,1))>="A",UPPER(MID(A1,7,1))<="Z"))

Alternative:
=AND(LEN(A1)=13,ISNUMBER(--LEFT(A1,6)),ISNUMBER(--RIGHT(A1,6)),NOT(ISNUMBER(--MID(A1, 7, 1))))

Greg

 

[David Biddulph]

Yes, you're right about the parentheses, but interestingly I get no
difference in the behaviour when I test the formula (old and revised) with
non-alphabetic characters in that position in the string. It looks as if
even the characters with an ASCII code above that for Z are rejected on the
=UPPER(MID(A1,7,1))>="A" test, so perhaps the =UPPER(MID(A1,7,1))<="Z" test
is redundant?

 

[Greg Wilson]

I don't know how the sort order is decided. It's not simply based on
character code since, for example
="[" > "A"
returns False. The above character codes are respectively 91 and 65.

The UPPER in the formula also appears to be redundant since lower case and
upper case are treated equal. For example:
="a"="A"
returns TRUE.

Based on limited testing, it appears that all alphabetics are treated as
greater than any other character and sort correctly relative to each other
*with lower and upper case treated equal*. Also, numbers are greater than any
other character except alphabetics. For example:
="1" > "["
returns TRUE while these character codes are respectively 49 and 91. Numbers
also sort correctly relative to each other.

All other characters appear to sort correctly relative to each other based
on character code but are less than both alphabetics and numbers. To
summarize:

Alphabetics (lower and upper same) > numbers > all other characters

Again, all of this based on very limited testing.

Greg

 

[STRAC]

Forgive me for taking so long to reply back...I was trying to make this thing
work for me...

If I take the followng stock numbers and place them in column A:
392001C074866
00144SPXFRM2A1
389852424040410
392001C044B236
3920PSA274N
9999080002158

The formuli you both gave me return results of:
TRUE
FALSE
FALSE
TRUE
FALSE
FALSE

For my purposes, only cells A1 and A6 are true. In looking at the data in
the cells, it appears that there are some embedded spaces at the ends of the
cell, meaning that the cell length (or string length) is really 16 vice the
13 I told you about earlier. My rudimentary understanding allowed me to
change the formula to reflect (LEN(A1)=16, however, no manner of hair pulling
or teeth gnashing got me to figure out how to get the others to come out
correctly.

Idealy I would like to get a false on any string longer than 13 (in a cell
which contains 16 "spaces"), anything which isn't a pure numeric (something I
failed to mention earlier and failed to figure out on my own), and anything
which has an alpha character in anything but the 7th spot.

Thanks in advance for your earlier help!

 

[David Biddulph]

I don't know how you get TRUE for the 4th line. I get false, either from my
original formula, or from either of Greg Wilson's suggestions, or from
another simplification
=AND(LEN(A1)=13,ISNUMBER(--LEFT(A1,6)),ISNUMBER(--RIGHT(A1,6)),MID(A1,7,1)>="A")Do I gather than you are now saying you'll allow either NNNNNNANNNNNN orNNNNNNNNNNNNN ?If so, you could use =AND(LEN(A1)=13,ISNUMBER(--LEFT(A1,6)),ISNUMBER(--RIGHT(A1,6)),OR(MID(A1,7,1)>="A",ISNUMBER(--MID(A1,7,1))))If you are also saying that you are including trailing spaces [or perhapsother characters such as CHAR(160)] in your string, then you can modify theabove formula appropriately.--David Biddulph"STRAC" <[email protected]> wrote in messagenews:[email protected]...> Forgive me for taking so long to reply back...I was trying to make thisthing> work for me...>> If I take the followng stock numbers and place them in column A:> 392001C074866> 00144SPXFRM2A1> 389852424040410> 392001C044B236> 3920PSA274N> 9999080002158>> The formuli you both gave me return results of:> TRUE> FALSE> FALSE> TRUE> FALSE> FALSE>> For my purposes, only cells A1 and A6 are true. In looking at the data in> the cells, it appears that there are some embedded spaces at the ends ofthe> cell, meaning that the cell length (or string length) is really 16 vicethe> 13 I told you about earlier. My rudimentary understanding allowed me to> change the formula to reflect (LEN(A1)=16, however, no manner of hairpulling> or teeth gnashing got me to figure out how to get the others to come out> correctly.>> Idealy I would like to get a false on any string longer than 13 (in a cell> which contains 16 "spaces"), anything which isn't a pure numeric(something I> failed to mention earlier and failed to figure out on my own), andanything> which has an alpha character in anything but the 7th spot.>> Thanks in advance for your earlier help!>> "Greg Wilson" wrote:>>> I don't know how the sort order is decided. It's not simply based on>> character code since, for example>> ="[" > "A">> returns False. The above character codes are respectively 91 and 65.>>>> The UPPER in the formula also appears to be redundant since lower caseand>> upper case are treated equal. For example:>> ="a"="A">> returns TRUE.>>>> Based on limited testing, it appears that all alphabetics are treated as>> greater than any other character and sort correctly relative to eachother>> *with lower and upper case treated equal*. Also, numbers are greater thanany>> other character except alphabetics. For example:>> ="1" > "[">> returns TRUE while these character codes are respectively 49 and 91.Numbers>> also sort correctly relative to each other.>>>> All other characters appear to sort correctly relative to each otherbased>> on character code but are less than both alphabetics and numbers. To>> summarize:>>>> Alphabetics (lower and upper same) > numbers > all other characters>>>> Again, all of this based on very limited testing.>>>> Greg

 

[Greg Wilson]

I don't get the same result using either David's or my formulas. I only get
the first one as TRUE since it is the only one with 13 chars that also has
the first 6 characters as numbers followed by a letter at the 7th position
and then 6 more numbers. This assuming trailing spaces are removed.

My 2nd take is that the following conditions must exist:
1. The length when leading/trailing spaces are removed must be 13
2. If it is entirely numeric (and 13 chars) then it is OK (change of
understanding)
3. If it is mixed numeric and alphabetic then only the character at the 7th
position can be a letter - i.e. the first 6 must be numbers and last 6 also
must be numbers

Try:
=AND(LEN(TRIM(A1)) =13, OR(ISNUMBER(--A1),
AND(ISNUMBER(--LEFT(TRIM(A1),6)),ISNUMBER(--RIGHT(TRIM(A1),6)),NOT(ISNUMBER(--MID(TRIM(A1), 7, 1))))))

Not tested.

Greg

Forgive me for taking so long to reply back...I was trying to make this thing
work for me...

If I take the followng stock numbers and place them in column A:
392001C074866
00144SPXFRM2A1
389852424040410
392001C044B236
3920PSA274N
9999080002158

The formuli you both gave me return results of:
TRUE
FALSE
FALSE
TRUE
FALSE
FALSE

For my purposes, only cells A1 and A6 are true. In looking at the data in
the cells, it appears that there are some embedded spaces at the ends of the
cell, meaning that the cell length (or string length) is really 16 vice the
13 I told you about earlier. My rudimentary understanding allowed me to
change the formula to reflect (LEN(A1)=16, however, no manner of hair pulling
or teeth gnashing got me to figure out how to get the others to come out
correctly.

Idealy I would like to get a false on any string longer than 13 (in a cell
which contains 16 "spaces"), anything which isn't a pure numeric (something I
failed to mention earlier and failed to figure out on my own), and anything
which has an alpha character in anything but the 7th spot.

Thanks in advance for your earlier help!

I don't know how the sort order is decided. It's not simply based on
character code since, for example
="[" > "A"
returns False. The above character codes are respectively 91 and 65.

The UPPER in the formula also appears to be redundant since lower case and
upper case are treated equal. For example:
="a"="A"
returns TRUE.

Based on limited testing, it appears that all alphabetics are treated as
greater than any other character and sort correctly relative to each other
*with lower and upper case treated equal*. Also, numbers are greater than any
other character except alphabetics. For example:
="1" > "["
returns TRUE while these character codes are respectively 49 and 91. Numbers
also sort correctly relative to each other.

All other characters appear to sort correctly relative to each other based
on character code but are less than both alphabetics and numbers. To
summarize:

Alphabetics (lower and upper same) > numbers > all other characters

Again, all of this based on very limited testing.

Greg

 

[STRAC]

You guys are truly the best! THANK-YOU!!
I will try these formuli and let you know the results.

I don't get the same result using either David's or my formulas. I only get
the first one as TRUE since it is the only one with 13 chars that also has
the first 6 characters as numbers followed by a letter at the 7th position
and then 6 more numbers. This assuming trailing spaces are removed.

My 2nd take is that the following conditions must exist:
1. The length when leading/trailing spaces are removed must be 13
2. If it is entirely numeric (and 13 chars) then it is OK (change of
understanding)
3. If it is mixed numeric and alphabetic then only the character at the 7th
position can be a letter - i.e. the first 6 must be numbers and last 6 also
must be numbers

Try:
=AND(LEN(TRIM(A1)) =13, OR(ISNUMBER(--A1),
AND(ISNUMBER(--LEFT(TRIM(A1),6)),ISNUMBER(--RIGHT(TRIM(A1),6)),NOT(ISNUMBER(--MID(TRIM(A1), 7, 1))))))

Not tested.

Greg

Forgive me for taking so long to reply back...I was trying to make this thing
work for me...

If I take the followng stock numbers and place them in column A:
392001C074866
00144SPXFRM2A1
389852424040410
392001C044B236
3920PSA274N
9999080002158

The formuli you both gave me return results of:
TRUE
FALSE
FALSE
TRUE
FALSE
FALSE

For my purposes, only cells A1 and A6 are true. In looking at the data in
the cells, it appears that there are some embedded spaces at the ends of the
cell, meaning that the cell length (or string length) is really 16 vice the
13 I told you about earlier. My rudimentary understanding allowed me to
change the formula to reflect (LEN(A1)=16, however, no manner of hair pulling
or teeth gnashing got me to figure out how to get the others to come out
correctly.

Idealy I would like to get a false on any string longer than 13 (in a cell
which contains 16 "spaces"), anything which isn't a pure numeric (something I
failed to mention earlier and failed to figure out on my own), and anything
which has an alpha character in anything but the 7th spot.

Thanks in advance for your earlier help!

I don't know how the sort order is decided. It's not simply based on
character code since, for example
="[" > "A"
returns False. The above character codes are respectively 91 and 65.

The UPPER in the formula also appears to be redundant since lower case and
upper case are treated equal. For example:
="a"="A"
returns TRUE.

Based on limited testing, it appears that all alphabetics are treated as
greater than any other character and sort correctly relative to each other
*with lower and upper case treated equal*. Also, numbers are greater than any
other character except alphabetics. For example:
="1" > "["
returns TRUE while these character codes are respectively 49 and 91. Numbers
also sort correctly relative to each other.

All other characters appear to sort correctly relative to each other based
on character code but are less than both alphabetics and numbers. To
summarize:

Alphabetics (lower and upper same) > numbers > all other characters

Again, all of this based on very limited testing.

Greg