if formula #value

The problem is, in case you couldn't follow along, that the OP keeps adding
stipulations to the data.
Rick and I have ALSO provided working formulas for the data as given, but
the OP has unequivocally stated "I am still trying to elicit the rules." So
are you telling me that the formulas that you and Ron have given that work on
the so far given data will work on all subsequent data, and the formulas that
Rick and I have given that work so far on the given data will NOT work on
subsequent data? The OP stated that they haven't given us all the rules of
the format for the part number. How can you be sure your formulas will cover
rules not listed?
I think we should wait until the OP actually clarifies the rules of the old
part numbering system.
--
** John C **

"Harlan Grove" wrote:

"Rick Rothstein" wrote...
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?

John C johnc@stateofdenial wrote...
The problem is, in case you couldn't follow along, that the OP keeps adding
stipulations to the data.

Actually I was amused by the following exchange.

http://groups.google.com/group/micro...0bc73ae3e2f29d

in which OP stated

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

to which Rick responded in
http://groups.google.com/group/micro...196d63469b3dca

'Does this formula do what you want?

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

to which the OP responded in
http://groups.google.com/group/micro...b9099c93b49cb7

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

When the OP STARTS OFF saying there could be extraneous characters at
the end of the string, then a respondent asks the OP whether they've
tried a simplistic RIGHT call, and the OP follows up politely saying
it doesn't work, it's just a bit rich for the respondent to begin
complaining about lack of specs.

Rick and I have ALSO provided working formulas for the data as given, . . .

Making several assumptions, several of which required hardcoding the
position of the K or B or hyphen. Hardcoding character position may
have happened to match a few samples the OP provided, but it's clearly
suboptimal. While you did state your assumptions, it's nevertheless
blindingly obvious you didn't pay much if any attention to the formula
in the OP's first follow-up,

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

Maybe that's too difficult for you to decipher, but to me it shows
clearly that the OP wants to find the first K or B after the first
hyphen. Nothing at all about ABSOLUTE character positions, ONLY the
RELATIVE position that the K or B is to the RIGHT of the FIRST hyphen.

To me, the problem seems to be either that the OP didn't realize FIND
returns #VALUE! if the substring sought doesn't occur in the string
searched or that IF won't treat error values as FALSE.

the OP has unequivocally stated "I am still trying to elicit the rules." So
are you telling me that the formulas that you and Ron have given that work on
the so far given data will work on all subsequent data, and the formulas that
Rick and I have given that work so far on the given data will NOT work on
subsequent data?

From the OP in http://groups.google.com/group/micro...8560fc12fa367e

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

Is that too ambiguous for you?

The OP stated that they haven't given us all the rules of
the format for the part number. How can you be sure your formulas will cover
rules not listed?
....

Either there's a hyphen involved or there isn't. If a hyphen's
involved, then the K or B sought seems to follow one of the hyphens.
Maybe that's not yet completely specified, but if it's always after a
specific hyphen but not necessarily at a specific absolute character
position, then my last set or reguar expression formulas should cover
all reasonable possibilities. If it's always after the first/leftmost
hyphen, Ron and I already provided working formulas that DO handle any
string lengths Excel itself can accommodate, any arbitrary characters
between the hyphen and the K or B and any arbitrary characters
following the K or B. Seems reasonably comprehensive.

Only if no hyphen is involved would absolute character position become
essential.

Hi Harlan,

I still report that:

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

I truly enjoyed your post! I feel like someone really understood what
I was saying.

Thanks again Harlan,

Dan

I realize the error of my ways, lawks a mussy, harlan is the brightest man on
the planet, he guesses at specs with the best of them, lawks a mussy, yessa
massa.

Don't you have some halls to monitor? Some bathroom passes to check?

The simple fact is, and I really really don't care what you think about it,
the OP gave ambiguous information. My formulas that I gave both work for the
criteria that the OP did give. I know you think you are the best thing since
sliced bread, but let's face it, your act is old and stale, and has green
mold on the edges. If you truly want to be an asset to the community, perhaps
you should remember that asset is a 5 letter word, and doesn't just stop
after 3 letters.
--
** John C **


"Harlan Grove" wrote:

John C johnc@stateofdenial wrote...
The problem is, in case you couldn't follow along, that the OP keeps adding
stipulations to the data.

Actually I was amused by the following exchange.

http://groups.google.com/group/micro...0bc73ae3e2f29d

in which OP stated

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

to which Rick responded in
http://groups.google.com/group/micro...196d63469b3dca

'Does this formula do what you want?

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

to which the OP responded in
http://groups.google.com/group/micro...b9099c93b49cb7

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

When the OP STARTS OFF saying there could be extraneous characters at
the end of the string, then a respondent asks the OP whether they've
tried a simplistic RIGHT call, and the OP follows up politely saying
it doesn't work, it's just a bit rich for the respondent to begin
complaining about lack of specs.

Rick and I have ALSO provided working formulas for the data as given, . . .

Making several assumptions, several of which required hardcoding the
position of the K or B or hyphen. Hardcoding character position may
have happened to match a few samples the OP provided, but it's clearly
suboptimal. While you did state your assumptions, it's nevertheless
blindingly obvious you didn't pay much if any attention to the formula
in the OP's first follow-up,

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

Maybe that's too difficult for you to decipher, but to me it shows
clearly that the OP wants to find the first K or B after the first
hyphen. Nothing at all about ABSOLUTE character positions, ONLY the
RELATIVE position that the K or B is to the RIGHT of the FIRST hyphen.

To me, the problem seems to be either that the OP didn't realize FIND
returns #VALUE! if the substring sought doesn't occur in the string
searched or that IF won't treat error values as FALSE.

the OP has unequivocally stated "I am still trying to elicit the rules." So
are you telling me that the formulas that you and Ron have given that work on
the so far given data will work on all subsequent data, and the formulas that
Rick and I have given that work so far on the given data will NOT work on
subsequent data?

From the OP in http://groups.google.com/group/micro...8560fc12fa367e

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

Is that too ambiguous for you?

The OP stated that they haven't given us all the rules of
the format for the part number. How can you be sure your formulas will cover
rules not listed?
....

Either there's a hyphen involved or there isn't. If a hyphen's
involved, then the K or B sought seems to follow one of the hyphens.
Maybe that's not yet completely specified, but if it's always after a
specific hyphen but not necessarily at a specific absolute character
position, then my last set or reguar expression formulas should cover
all reasonable possibilities. If it's always after the first/leftmost
hyphen, Ron and I already provided working formulas that DO handle any
string lengths Excel itself can accommodate, any arbitrary characters
between the hyphen and the K or B and any arbitrary characters
following the K or B. Seems reasonably comprehensive.

Only if no hyphen is involved would absolute character position become
essential.

John C johnc@stateofdenial wrote...
....
Don't you have some halls to monitor? Some bathroom passes to check?
....

Nope. Just enjoy pointing out classless, brainless bozos like you.

The simple fact is, and I really really don't care what you think about it,
the OP gave ambiguous information. . . .
....

Nope. You just couldn't figure it out. And now you can't bring
yourself to admit you couldn't figure it out. At least the rest of us
have a very clear notion of your (lack of) character.

BTW, see the OP's latest follow-up. Better luck next time.

Bottomline, both my formulas worked with the OP's criteria. I realize you
hate it when people talk back to you, it's kind of funny really if you think
about it. Word of advice, move out of your mommy's basement, and find a real
job.

Oh, and find the cajones to be an original responder, rather than just
trying to pick apart every one elses' responses.

By the way, my formulas, both of them, worked. I figured I would have to
mention it twice since you are too, well, stupid to catch on.
--
** John C **

"Harlan Grove" wrote:

John C johnc@stateofdenial wrote...
....
Don't you have some halls to monitor? Some bathroom passes to check?
....

Nope. Just enjoy pointing out classless, brainless bozos like you.

The simple fact is, and I really really don't care what you think about it,
the OP gave ambiguous information. . . .
....

Nope. You just couldn't figure it out. And now you can't bring
yourself to admit you couldn't figure it out. At least the rest of us
have a very clear notion of your (lack of) character.

BTW, see the OP's latest follow-up. Better luck next time.

John C johnc@stateofdenial wrote...
Bottomline, both my formulas worked with the OP's criteria. . . .

Not quite. Your first formula worked with the OP's sample data, but
ignored the OP's formula, which the OP provided in a follow-up, that
indicated the OP was looking for a K or B after a hyphen.

You don't think that was clear. That's your problem and/or failure.

Oh, and find the cajones to be an original responder, rather than just
trying to pick apart every one elses' responses.

I provide a greater service by picking apart bloviating ignoramuses
like you who can't figure out specs unless they're spelled out in very
simple language.

By the way, my formulas, both of them, worked. I figured I would have to
mention it twice since you are too, well, stupid to catch on.

Again, they worked with the sample data the OP provided. They weren't
general solutions. The first was better than the second. The first
used the charactere position shown in the OP's sample data. The second
required you to ADD a caveat to the OP's specs, namely, that there
wouldn't be other instances of K or B in the part number. However, the
OP's 3rd follow-up, which PRECEDED your 2nd response included the
following additional sample data

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

Is that not clear to you that the part numbers COULD include arbitrary
letters? Did the OP need to add a bullet point in 24 point type size
to lead you to consider that it would be unwise to look for a K or a B
anywhere?

OK, fine, you don't want to try to figure out OPs' actual specs based
on limited samples provided in their postings. But you might consider
paying some attention to their FORMULAS, especially as in this case
you were the one who asked the OP to provide the formula they were
originally trying to use. It's pretty obvious you either ignored it or
didn't understand it. Which was it?