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).

=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

"STRAC" wrote in message
...
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).

I put this formula in the first available cell to the right of my data and
copied it down through the spreadsheet. Each row returned false. Should I
have placed this somewhere else?

"David Biddulph" wrote:

=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

"STRAC" wrote in message
...
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).

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
--
David Biddulph

"STRAC" wrote in message
...
I put this formula in the first available cell to the right of my data and
copied it down through the spreadsheet. Each row returned false. Should
I
have placed this somewhere else?

"David Biddulph" wrote:

=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

"STRAC" wrote in message
...
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, 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" wrote:

=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

"STRAC" wrote in message
...
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).

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?
--
David Biddulph

"Greg Wilson" wrote in message
...
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" wrote:

=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

"STRAC" wrote in message
...
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).

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

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!

"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 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