if formula #value
dan dungan
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
--
** John C **
Bernard Liengme
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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"dan dungan" <stage...@yahoo.com> wrote in message
news:6ee669f5-893f-4f14...@l33g2000pri.googlegroups.com...
dan dungan
=IF(FIND("K",UPPER(A3),FIND("-",A3,1)),4,IF(FIND("B",UPPER(A3),FIND
("-",A3,1)),4,1))
dan dungan
end of the company's so I didn't use the right function.
Rick Rothstein
=IF(OR(RIGHT(A3)={"B","K"}),4,1)
--
Rick (MVP - Excel)
"dan dungan" <stage...@yahoo.com> wrote in message
news:b2e8cdc4-7994-4551...@c2g2000pra.googlegroups.com...
dan dungan
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
wrote:
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
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?
--
Rick (MVP - Excel)
"dan dungan" <stage...@yahoo.com> wrote in message
news:bee0b1da-d804-472c...@d10g2000pra.googlegroups.com...
John C
=IF(OR(RIGHT(LEFT(A2,17))="K",RIGHT(LEFT(A2,17))="B"),1,0)
--
** John C **
Rick Rothstein
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.
--
Rick (MVP - Excel)
"John C" <johnc@stateofdenial> wrote in message
news:36A6931F-CDCA-4148...@microsoft.com...
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
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
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.
--
Rick (MVP - Excel)
"dan dungan" <stage...@yahoo.com> wrote in message
news:87edee36-70cb-4005...@a29g2000pra.googlegroups.com...
dan dungan
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
John C
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)
--
** John C **
dan dungan
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
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)?
--
Rick (MVP - Excel)
"dan dungan" <stage...@yahoo.com> wrote in message
news:ae43e2a6-fce9-4866...@d10g2000pra.googlegroups.com...
dan dungan
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
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
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
>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/microsoft.public.excel.worksheet.functions/msg/ab0bc73ae3e2f29d
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/microsoft.public.excel.worksheet.functions/msg/d9196d63469b3dca
'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/microsoft.public.excel.worksheet.functions/msg/0fb9099c93b49cb7
'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/microsoft.public.excel.worksheet.functions/msg/668560fc12fa367e
'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.
dan dungan
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
John C
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:
> ....
Harlan Grove
...
>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
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. . . .
> ....
Harlan Grove
>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?