IF function problems

  • Thread starter Thread starter mmcap
  • Start date Start date
M

mmcap

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?
 
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
 
The main problem that I am experiencing is it won’t 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 doesn’t 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 isn’t 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
 
The main problem that I am experiencing is it won’t 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 doesn’t 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 isn’t 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
 
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.
 
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
 
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

formula finds values in apparently blank cells 1
Excel 2010 If Then logic - with time 2
if statement 5
Need a little math help in making a formula 2
TRUE/FALSE function in IF statement 2
automated cut and paste using regex 2
#Name? Error in User Defined VB Function 2
Vlookup function wont work for copied data 2

Back
Top