I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like T45T-6847 and
307-485. Is there a way to get the logic to return €œTRUE€� for these kinds of
numbers?

I'm not sure two example numbers define "these kinds of numbers" very well.
Is your general rule something like letters and digits *only* with a dash
between them, or is there other variations (different symbols than a dash
between them, a restricted set of other symbols in place of the letters,
etc.)? Is there any minimum number of digits that must appear on either side
of the dash? Where are the letters permitted to be? In the first part of the
entry in front of the dash only? In certain positions (beginning and/or end
of a number part)? Any other restrictions that you can think of?

--
Rick (MVP - Excel)


"mmcap" wrote in message
...
I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like T45T-6847 and
307-485. Is there a way to get the logic to return €œTRUE€� for these kinds
of
numbers?

Hi,

And what exactly do you mean by "like"?

=IF(OR(A1="T45T-6847",A1="307-485"),TRUE)

Would do it for these specific cases. And what do you want the IF to return
if they don't match?

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"mmcap" wrote:

I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like T45T-6847 and
307-485. Is there a way to get the logic to return €œTRUE€� for these kinds of
numbers?

The main problem that I am experiencing is it wont return the value of
€œTRUE€� for any kind of number except regular numbers like 1, 2, 3 and so on
it shows #REF!. Here is an example
IF(B10,INDEX(C14:C18,MAX((list1=B4)*ROW(C14:C18))-13),€�€�). If any letters or
dashes are in cell B10 it returns #REF! in the logic portion so the rest of
the IF formula doesnt work correctly either. I just want the cell that
contains the formula to be blank unless I put a number in cell B10 most of
which will contain letters and dashes. I know this isnt the big picture. It
would take too long to explain. But if I can get this to work that will solve
a big problem.
Many Thanks
Norman


"Rick Rothstein" wrote:

I'm not sure two example numbers define "these kinds of numbers" very well.
Is your general rule something like letters and digits *only* with a dash
between them, or is there other variations (different symbols than a dash
between them, a restricted set of other symbols in place of the letters,
etc.)? Is there any minimum number of digits that must appear on either side
of the dash? Where are the letters permitted to be? In the first part of the
entry in front of the dash only? In certain positions (beginning and/or end
of a number part)? Any other restrictions that you can think of?

--
Rick (MVP - Excel)


"mmcap" wrote in message
...
I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like T45T-6847 and
307-485. Is there a way to get the logic to return €œTRUE€� for these kinds
of
numbers?

The main problem that I am experiencing is it wont return the value of
€œTRUE€� for any kind of number except regular numbers like 1, 2, 3 and so on
it shows #REF!. Here is an example
IF(B10,INDEX(C14:C18,MAX((list1=B4)*ROW(C14:C18))-13),€�€�). If any letters or
dashes are in cell B10 it returns #REF! in the logic portion so the rest of
the IF formula doesnt work correctly either. I just want the cell that
contains the formula to be blank unless I put a number in cell B10 most of
which will contain letters and dashes. I know this isnt the big picture. It
would take too long to explain. But if I can get this to work that will solve
a big problem.
Many Thanks
Norman


"Shane Devenshire" wrote:

Hi,

And what exactly do you mean by "like"?

=IF(OR(A1="T45T-6847",A1="307-485"),TRUE)

Would do it for these specific cases. And what do you want the IF to return
if they don't match?

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"mmcap" wrote:

I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like T45T-6847 and
307-485. Is there a way to get the logic to return €œTRUE€� for these kinds of
numbers?

The items you posted and called "numbers" are, of course, not numbers. As
such, there is no standard functions that will do what you want. In order to
do what you are looking for, a formula must be custom designed to handle
these "numbers". In order to do that (remember, no one here knows how your
business runs or what these "numbers" mean), you must describe to us the
allowable "shape" (how the letters, non-letters and digits can be arranged)
for your "numbers". That is why I asked you the questions I did (and which
you didn't answer) in my last post. If you can answer those questions and,
if my questions didn't touch completely on how your "numbers" are allowed to
be formed, provide any other insight into their allowable "shape", maybe
someone here will be able to give you the formula you are seeking.

--
Rick (MVP - Excel)


"mmcap" wrote in message
...
The main problem that I am experiencing is it wont return the value of
€œTRUE€� for any kind of number except regular numbers like 1, 2, 3 and so
on
it shows #REF!. Here is an example
IF(B10,INDEX(C14:C18,MAX((list1=B4)*ROW(C14:C18))-13),€�€�). If any letters
or
dashes are in cell B10 it returns #REF! in the logic portion so the rest
of
the IF formula doesnt work correctly either. I just want the cell that
contains the formula to be blank unless I put a number in cell B10 most of
which will contain letters and dashes. I know this isnt the big picture.
It
would take too long to explain. But if I can get this to work that will
solve
a big problem.
Many Thanks
Norman


"Rick Rothstein" wrote:

I'm not sure two example numbers define "these kinds of numbers" very
well.
Is your general rule something like letters and digits *only* with a dash
between them, or is there other variations (different symbols than a dash
between them, a restricted set of other symbols in place of the letters,
etc.)? Is there any minimum number of digits that must appear on either
side
of the dash? Where are the letters permitted to be? In the first part of
the
entry in front of the dash only? In certain positions (beginning and/or
end
of a number part)? Any other restrictions that you can think of?

--
Rick (MVP - Excel)


"mmcap" wrote in message
...
I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like T45T-6847
and
307-485. Is there a way to get the logic to return €œTRUE€� for these
kinds
of
numbers?

I apologize if my answer seemed cryptic. In the attempt to keep things brief
I overlooked the need for precision. This is the exact format in which all of
the €œnumbers€� will be. 205-135 (three numbers dash three numbers) in some
exceptions there may be additions to the last segment like 307-451/1
307-451/2 307-451/3 always with a slash.
The last form would be T80P-4946-A
(letter,number,number,letter €“ number,number,number,number €“ letter). These
are part numbers for which I am trying to make a searchable inventory.

The numbers in the following layout were just trial numbers and are not in
the correct format.
# BOX#
Search For # t1 BOX#1
Search For # t3 BOX#1
Search For # t4 BOX#2
Search For # t6 BOX#2
Search For # t7 BOX#3
Search For # t9 BOX#3
Search For # t10 BOX#4
Search For # t12 BOX#4
Search For # t15 BOX#5
Search For # 1 BOX#5

# # #
BOX#1 t1 t2 t3
BOX#2 t4 t5 t6
BOX#3 t7 t8 t9
BOX#4 t10 t11 t12
BOX#5 t13 t14 t15

The top section is where I would search for multiple numbers and the bottom
section is where the inventory numbers would reside. Once again any help is
appreciated
Norman


"Rick Rothstein" wrote:

The items you posted and called "numbers" are, of course, not numbers. As
such, there is no standard functions that will do what you want. In order to
do what you are looking for, a formula must be custom designed to handle
these "numbers". In order to do that (remember, no one here knows how your
business runs or what these "numbers" mean), you must describe to us the
allowable "shape" (how the letters, non-letters and digits can be arranged)
for your "numbers". That is why I asked you the questions I did (and which
you didn't answer) in my last post. If you can answer those questions and,
if my questions didn't touch completely on how your "numbers" are allowed to
be formed, provide any other insight into their allowable "shape", maybe
someone here will be able to give you the formula you are seeking.

--
Rick (MVP - Excel)


"mmcap" wrote in message
...
The main problem that I am experiencing is it wont return the value of
€œTRUE€� for any kind of number except regular numbers like 1, 2, 3 and so
on
it shows #REF!. Here is an example
IF(B10,INDEX(C14:C18,MAX((list1=B4)*ROW(C14:C18))-13),€�€�). If any letters
or
dashes are in cell B10 it returns #REF! in the logic portion so the rest
of
the IF formula doesnt work correctly either. I just want the cell that
contains the formula to be blank unless I put a number in cell B10 most of
which will contain letters and dashes. I know this isnt the big picture.
It
would take too long to explain. But if I can get this to work that will
solve
a big problem.
Many Thanks
Norman


"Rick Rothstein" wrote:

I'm not sure two example numbers define "these kinds of numbers" very
well.
Is your general rule something like letters and digits *only* with a dash
between them, or is there other variations (different symbols than a dash
between them, a restricted set of other symbols in place of the letters,
etc.)? Is there any minimum number of digits that must appear on either
side
of the dash? Where are the letters permitted to be? In the first part of
the
entry in front of the dash only? In certain positions (beginning and/or
end
of a number part)? Any other restrictions that you can think of?

--
Rick (MVP - Excel)


"mmcap" wrote in message
...
I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like T45T-6847
and
307-485. Is there a way to get the logic to return €œTRUE€� for these
kinds
of
numbers?

Okay, now I understand your part number setup, and I thought I understood
from your first post what you wanted to do with them, but with the
additional information you added to your last post, I am no longer sure of
that. I see the word "search" in the sample layout you posted... are you
trying to lookup something in a table for a given part number? Your sample
layout didn't make much sense to me (I'm thinking BOX and t1, t2, etc. mean
something to you in your business model, but I can't figure out what it is).
Can you give a layout with *real* values in them and a description of what
you have and what you are trying to do with it?

--
Rick (MVP - Excel)


"mmcap" wrote in message
...
I apologize if my answer seemed cryptic. In the attempt to keep things
brief
I overlooked the need for precision. This is the exact format in which all
of
the €œnumbers€� will be. 205-135 (three numbers dash three numbers) in some
exceptions there may be additions to the last segment like 307-451/1
307-451/2 307-451/3 always with a slash.
The last form would be T80P-4946-A
(letter,number,number,letter €“ number,number,number,number €“ letter).
These
are part numbers for which I am trying to make a searchable inventory.

The numbers in the following layout were just trial numbers and are not in
the correct format.
# BOX#
Search For # t1 BOX#1
Search For # t3 BOX#1
Search For # t4 BOX#2
Search For # t6 BOX#2
Search For # t7 BOX#3
Search For # t9 BOX#3
Search For # t10 BOX#4
Search For # t12 BOX#4
Search For # t15 BOX#5
Search For # 1 BOX#5

# # #
BOX#1 t1 t2 t3
BOX#2 t4 t5 t6
BOX#3 t7 t8 t9
BOX#4 t10 t11 t12
BOX#5 t13 t14 t15

The top section is where I would search for multiple numbers and the
bottom
section is where the inventory numbers would reside. Once again any help
is
appreciated
Norman


"Rick Rothstein" wrote:

The items you posted and called "numbers" are, of course, not numbers. As
such, there is no standard functions that will do what you want. In order
to
do what you are looking for, a formula must be custom designed to handle
these "numbers". In order to do that (remember, no one here knows how
your
business runs or what these "numbers" mean), you must describe to us the
allowable "shape" (how the letters, non-letters and digits can be
arranged)
for your "numbers". That is why I asked you the questions I did (and
which
you didn't answer) in my last post. If you can answer those questions
and,
if my questions didn't touch completely on how your "numbers" are allowed
to
be formed, provide any other insight into their allowable "shape", maybe
someone here will be able to give you the formula you are seeking.

--
Rick (MVP - Excel)


"mmcap" wrote in message
...
The main problem that I am experiencing is it wont return the value of
€œTRUE€� for any kind of number except regular numbers like 1, 2, 3 and
so
on
it shows #REF!. Here is an example
IF(B10,INDEX(C14:C18,MAX((list1=B4)*ROW(C14:C18))-13),€�€�). If any
letters
or
dashes are in cell B10 it returns #REF! in the logic portion so the
rest
of
the IF formula doesnt work correctly either. I just want the cell
that
contains the formula to be blank unless I put a number in cell B10 most
of
which will contain letters and dashes. I know this isnt the big
picture.
It
would take too long to explain. But if I can get this to work that will
solve
a big problem.
Many Thanks
Norman


"Rick Rothstein" wrote:

I'm not sure two example numbers define "these kinds of numbers" very
well.
Is your general rule something like letters and digits *only* with a
dash
between them, or is there other variations (different symbols than a
dash
between them, a restricted set of other symbols in place of the
letters,
etc.)? Is there any minimum number of digits that must appear on
either
side
of the dash? Where are the letters permitted to be? In the first part
of
the
entry in front of the dash only? In certain positions (beginning
and/or
end
of a number part)? Any other restrictions that you can think of?

--
Rick (MVP - Excel)


"mmcap" wrote in message
...
I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like
T45T-6847
and
307-485. Is there a way to get the logic to return €œTRUE€� for these
kinds
of
numbers?

IF(B10="","",INDEX(C14:C18,MAX((list1=B4)*ROW(C14: C18))-13))


"mmcap" wrote:

The main problem that I am experiencing is it wont return the value of
€œTRUE€� for any kind of number except regular numbers like 1, 2, 3 and so on
it shows #REF!. Here is an example
IF(B10,INDEX(C14:C18,MAX((list1=B4)*ROW(C14:C18))-13),€�€�). If any letters or
dashes are in cell B10 it returns #REF! in the logic portion so the rest of
the IF formula doesnt work correctly either. I just want the cell that
contains the formula to be blank unless I put a number in cell B10 most of
which will contain letters and dashes. I know this isnt the big picture. It
would take too long to explain. But if I can get this to work that will solve
a big problem.
Many Thanks
Norman


"Shane Devenshire" wrote:

Hi,

And what exactly do you mean by "like"?

=IF(OR(A1="T45T-6847",A1="307-485"),TRUE)

Would do it for these specific cases. And what do you want the IF to return
if they don't match?

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"mmcap" wrote:

I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like T45T-6847 and
307-485. Is there a way to get the logic to return €œTRUE€� for these kinds of
numbers?

It works perfect.
Thank you

"Teethless mama" wrote:

IF(B10="","",INDEX(C14:C18,MAX((list1=B4)*ROW(C14: C18))-13))


"mmcap" wrote:

The main problem that I am experiencing is it wont return the value of
€œTRUE€� for any kind of number except regular numbers like 1, 2, 3 and so on
it shows #REF!. Here is an example
IF(B10,INDEX(C14:C18,MAX((list1=B4)*ROW(C14:C18))-13),€�€�). If any letters or
dashes are in cell B10 it returns #REF! in the logic portion so the rest of
the IF formula doesnt work correctly either. I just want the cell that
contains the formula to be blank unless I put a number in cell B10 most of
which will contain letters and dashes. I know this isnt the big picture. It
would take too long to explain. But if I can get this to work that will solve
a big problem.
Many Thanks
Norman


"Shane Devenshire" wrote:

Hi,

And what exactly do you mean by "like"?

=IF(OR(A1="T45T-6847",A1="307-485"),TRUE)

Would do it for these specific cases. And what do you want the IF to return
if they don't match?

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"mmcap" wrote:

I cannot get the logic portion of the IF function to recognize an
alphanumeric entry. I need for it to recognize entries like T45T-6847 and
307-485. Is there a way to get the logic to return €œTRUE€� for these kinds of
numbers?