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

What formula?
--
** John C **


"dan dungan" wrote:

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

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"dan dungan" wrote in message
...
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

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 wrote...
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)

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.

Does this formula do what you want?

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

--
Rick (MVP - Excel)


"dan dungan" wrote in message
...
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.

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

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?

--
Rick (MVP - Excel)


"dan dungan" wrote in message
...
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

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)
--
** John C **


"dan dungan" wrote:

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

On Fri, 14 Nov 2008 10:35:14 -0800 (PST), dan dungan
wrote:

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