A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Add text automatically by recognized value



 
 
Thread Tools Display Modes
  #1  
Old April 16th, 2010, 08:19 PM posted to microsoft.public.excel.worksheet.functions
Peter Gonzalez[_2_]
external usenet poster
 
Posts: 15
Default Add text automatically by recognized value

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
  #2  
Old April 16th, 2010, 08:42 PM posted to microsoft.public.excel.worksheet.functions
Mitch
external usenet poster
 
Posts: 159
Default Add text automatically by recognized value

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

"Peter Gonzalez" wrote:

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

  #3  
Old April 16th, 2010, 08:51 PM posted to microsoft.public.excel.worksheet.functions
Ron@Buy
external usenet poster
 
Posts: 348
Default Add text automatically by recognized value

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

"Ron@Buy" wrote:

Peter
VLOOKUP is a possible solution:
Set up a looup table somewhere on your worksheet: say S1 to S5, enter the
first three digits of your item nimber. Then in cells T1 to T5 set up the
corresponding department name.
Now in column D same row as the first item number enter (assuming row 2):
=IF(C2="","",VLOOKUP(LEFT(C2,3),S$1:T$5,2,0))
This should give you what you require. Adjust column and row reference to
suit.
To allow for your master list of item numbers and department name to
increase Increase the number after the T in the formula.
Hope this helps

"Peter Gonzalez" wrote:

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

  #4  
Old April 16th, 2010, 09:00 PM posted to microsoft.public.excel.worksheet.functions
Peter Gonzalez[_2_]
external usenet poster
 
Posts: 15
Default Add text automatically by recognized value

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.

"Mitch" wrote:

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

"Peter Gonzalez" wrote:

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

  #5  
Old April 16th, 2010, 09:21 PM posted to microsoft.public.excel.worksheet.functions
Per Jessen[_2_]
external usenet poster
 
Posts: 189
Default Add text automatically by recognized value

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


On 16 Apr., 22:00, Peter Gonzalez
wrote:
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.



"Mitch" wrote:
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


"Peter Gonzalez" wrote:


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 * * *- Skjul tekst i anførselstegn -


- Vis tekst i anførselstegn -


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.