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.