Add text automatically by recognized value

[Peter Gonzalez]

I'm trying to create a formula where it recognizes the first three digits of
an item number at the same time entering the department name in the cell next
to it. So let's say the item number R33569 for dept. WRP, I would only need
it to recognize the R33 while entering WRP in the column next to the item
number.

This is what I got on my work sheet
Column C Item Number
R33569
R33456
R33447

In the column to the right or left doesn't matter which I need to say the
dept. name. So how can i do that without having go through the hundreds of
partnumbers that I have. let me know if you need more explanation.
I appreciate the help

 

[Mitch]

Hi Peter,

on the item column (column B) just use the below formula and drag it dawn
for the other items.

=IF($C2="WRP",LEFT($A2,3),"")

A B C
Item Dept
R33569 R33 WRP
R33456
R33447

Click yes if this helps

Thanks

 

[Ron@Buy]

Forgot to add drag down forula to the bottom of your list

 

[Peter Gonzalez]

That worked great. Sorry one more thing it's not too much trouble. Suppose I
have other item numbers for different departments in the same column that the
first three digits are different how could I add it to the formula so that it
recognizes those as well while also doing the same for their dept names.
Example:
R34 - RTF
S12 - FRAME
S14 - FRAME
K09 - MILL
K21 - MILL
S20 - MILL
etc.

 

[Per Jessen]

Hi

Set up a table in Sheet2 column A & B like the one in your example.

Now you can use this formula in sheet 1 to lookup the dept name for
the item number in A1:

=VLOOKUP(LEFT(A1;3);Sheet2!A1:B100;2;FALSE)

Regards,
Per