if formula #value

[dan dungan]

Hi,

In Excel 2000, I'm converting a company's part number to our part
number.

Company part number with band
440HS030NF2107-3B
440HS030NF2107-3K

our part number with band
27140S201440655

Company part number-no band
440HS030NF2107-3

our part number-no band
27140S201410655

The B in the company part number indicates a band.
If, instead of B, the company part number shows K, this also indicates
a band. In both of these cases our part number should have a 4 in the
11th position.

If there is no B or K, that indicates no band and there should be a 1
in the 11th position.

I'm using the following formula to determine if there is a b or k in
the part number. I returns #value. I'm wondering what I'm doing wrong.

Thanks,

Dan

 

[John C]

What formula?

 

[Bernard Liengme]

Had you shown us the formula we might have been or more help to you
However, you could try this:
=IF(OR(RIGHT(A1)="B", RIGHT(A1)="K"),"Found a B or a K","not a band")
This will return 1 or 0 depend on the presence of B or K at the end of A1's
text
=SUMPRODUCT(--(RIGHT(A1)={"B","K"}))
best wishes

 

[dan dungan]

My paste key broke

=IF(FIND("K",UPPER(A3),FIND("-",A3,1)),4,IF(FIND("B",UPPER(A3),FIND
("-",A3,1)),4,1))

 

[dan dungan]

I neglected to mention there could be other alpha characters at the
end of the company's so I didn't use the right function.

 

[Rick Rothstein]

Does this formula do what you want?

=IF(OR(RIGHT(A3)={"B","K"}),4,1)

 

[dan dungan]

Hi Rick,

It's close, but there could be other characters after the K or B

I could have:

440HS030NF2107-3KPT
440HS030NF2107-3KPT-063
440HS030NF2107-3PT
440HS030NF2107-3
440HS030NF2107-3BPT

So the right function doesn't always work.

Thanks,

Dan

 

[Ron Rosenfeld]

Hi,

In Excel 2000, I'm converting a company's part number to our part
number.

Company part number with band
440HS030NF2107-3B
440HS030NF2107-3K

our part number with band
27140S201440655

Company part number-no band
440HS030NF2107-3

our part number-no band
27140S201410655

The B in the company part number indicates a band.
If, instead of B, the company part number shows K, this also indicates
a band. In both of these cases our part number should have a 4 in the
11th position.

If there is no B or K, that indicates no band and there should be a 1
in the 11th position.

I'm using the following formula to determine if there is a b or k in
the part number. I returns #value. I'm wondering what I'm doing wrong.

Thanks,

Dan

Well, without your formula, can't tell what's wrong.

But try this to return either a 4 or a 1 depending on if there is a B or a K
after a hyphen in Company Part number:

=IF(OR(ISNUMBER(SEARCH("B",A1,FIND("-",A1))),
ISNUMBER(SEARCH("K",A1,FIND("-",A1)))),4,1)

--ron

 

[Rick Rothstein]

Okay, we need a little more information about your part numbers. Is the
first dash always the 15th character in the text? Can the number after the
dash be more than one digit? For the first example part number in you last
posting (440HS030NF2107-3KPT)... does the K make it a banded number or is it
a banded number only if the K (or B) is the only character after then
number?

 

[John C]

If the K or B is always the 17th character, you could try:
=IF(OR(RIGHT(LEFT(A2,17))="K",RIGHT(LEFT(A2,17))="B"),1,0)

 

[Rick Rothstein]

If your guess of the 17th character is correct, then here is a shorter form
for your formula...

=IF(OR(MID(A2,17,1)={"K","B"}),4,1)

where I used the 4 and 1 that the OP originally indicated he wanted.

 

[Harlan Grove]

My paste key broke

=IF(FIND("K",UPPER(A3),FIND("-",A3,1)),4,IF(FIND("B",UPPER(A3),
FIND("-",A3,1)),4,1))

If there were no K in A3, the 1st argument to IF would throw the
#VALUE! error.

Another alternative.

=IF(OR(COUNTIF(A3,"*-*"&{"K","B"}&"*")),4,1)

 

[dan dungan]

Hi Rick,

The "-" isn't always the 17th character.

The K or B determine if the part has a band.
The T indicates a shrink boot.
The P indicates passivate plating.

There is the possibility of another "-" and some numbers, -063 which
indicates other modifications.


So far, these two work the best:

Harlan's
=IF(OR(COUNTIF(A3,"*-*"&{"K","B"}&"*")),4,1)

or Ron's
=IF(OR(ISNUMBER(SEARCH("B",A1,FIND("-",A1))),
ISNUMBER(SEARCH("K",A1,FIND("-",A1)))),4,1)

Thanks,

Dan

 

[Rick Rothstein]

If you want a solution, you will have to give us the complete set of "rules"
that govern how a company part number is formatted... not piecemeal examples
as you think of them. Unless we know all the various "shapes" the company
part number can take, there is almost no way we will be able to guess a
complete solution for you.

 

[John C]

Is there ANY chance of K or B occurring in any of the part numbers anywhere
else in the part number? If K or B ONLY occur to indicate a band, then we can
work with that....Otherwise, as Rick asked, we need to know all the
rules/shapes of the part numbers in order to incorporate it into one all
encompassing formula.
If no other occurrances of K or B can exist other than to indicate a band
anywhere in the old part number, then this should work for you. I even
included the UPPER function as you did in your original formula:
=IF(AND(ISERROR(FIND("K",UPPER(A2))),ISERROR(FIND("B",UPPER(A2)))),1,4)

 

[dan dungan]

Rick,
I really appreciate your help and feedback.

My question was focusing on one portion of the part number. I wasn't
trying to ask you to guess a complete solution.

I'm still trying to elicit the rules. I was simply responding to your
questions.

These part numbers started before 1940. Thousands of new part number
have been
created every year since then. In the mean time, the part number
formats have changed with new business processes, government
directives and just plain human mistakes. But the parts exist and are
still being ordered to replace worn and defective parts.

Thanks again for your help.

Dan

 

[dan dungan]

Hi John,

I'm not sure about that. I'll have to elicit all the rules and get
back to you all. I apologize for not having all the information before
I asked the questions.

Thanks,

Dan

 

[Rick Rothstein]

Even if you don't want a complete solution, you have to give us more than
you have already. The think you have to realize is that while you have some
familiarity with these part numbers, we have no idea about them at all... we
only know what you tell us. That is why it is important to either tell us
the rules governing their shape or at least give us enough examples that we
can get an idea about them. For example, your examples all showed the dash
in a certain position, so we all made a guess that the dash is always in
that position, but then you told us later on that the dash can be in other
locations... do you see the problem that "hidden" information causes us?
Text parsing is almost always dependent on the rules behind the formation of
the text you want parsed... so we need to know more than you have told us so
far. Here are some questions that may help focus us in a little better. Is
there always at least one dash? Can the number following the dash be more
than one digit? If the letter following that number is a K or B and other
characters follow it, is it still a part number with a "band" (or is the
"rule" that the K or B must stand alone)?

 

[dan dungan]

Hi Rick,

I'm going to get the answers to your questions and describe all the
rules.

Then I'll get back with my questions.

Thanks,

Dan

 

[Harlan Grove]

Even if you don't want a complete solution, you have to give us more than
you have already. . . .

Really? From the OP's original samples and formula, it sure looks like
the OP wants to return 4 if there's either a K or a B following a
hyphen. There could be other characters between the hyphen and the K
or B, there could be arbitrary characters after the K or B, and the K
or B could appear at any character position > 2 (assuming there'd need
to be at least 1 character to the left of the hyphen).

If that's so, Ron Rosenfeld and I have already provided working
formulas. Our respective formulas would also work if the K or B in
question would follow the LEFTMOST hyphen.

If not, e.g., if there could be multiple hyphens and the K or B would
need to be to the right of the FINAL hyphen, that'd require either
character at a time parsing or regular expressions. For the former,
define a name like seq referring to the formula

=ROW(INDEX($1:$66536,1,1):INDEX($1:$65536,256,1))

and use a formula like

=IF(COUNT(SEARCH({"K","B"},A1,MATCH(2,1/(MID("-"&A1,seq,1)="-")))),
4,1)

For the latter, best to download and install Laurent Longre's
MOREFUNC.XLL and use a formula like

=IF(REGEX.COMP(A1,"^.*-[^-KB]*[KB][^-]*$"),4,1)

The regular expression approach is the only way to go if the K or B of
interest is after the 2nd or subsequent hyphen but possibly followed
by more hyphens. In that case, if the K or B follows the n_th hyphen
(n > 1), use

=IF(REGEX.COMP(A1,"^([^-]*-){n}[^-KB]*[KB]"),4,1)

[note that n here should be a literal number].

Is this still an ambiguous problem?