Hi,
I am trying to find a way to eliminate endings off of part numbers.
Below are the list of endings that I need to be eliminated from an extremely long list of variant part numbert. This list below may need to have additional endings added, but nothing will be removed. Please see attachment for example of part numbers that need below endings eliminated. Thanks!
R
T
G4
E4
RG4
RE4
TG4
TE4
G6
E6
RG6
RE6
TG6
TE6
/2K5
/3K
/250
/500
Last edited by VBA Noob; 11-04-2008 at 02:27 PM.
The part numbers in your sample file range from six to 16 characters. Your list is removing from one to four characters, but you did not give a "before" and "after" example with the reason/rule to be used to decide what gets removed.
Ben Van Johnson
I would assume that whatever the length of the part number, the end needs to be removed. So if 555-333-abctg6 were a number, he wants the tg6 removed.
put your list of things to be removed in say col c
then try in b2
dragged down
Please Login or Register to view this content.
you can add any 1,2,3,4 char text as required to list in col c
If the part numbers are in A2 down, and the suffixes to be removed are in a range called Sfx, then in B2 and copy down,
=IFERROR(LEFT(A2, LEN(A2) - LEN(INDEX(Sfx, MATCH(TRUE, RIGHT(A2, LEN(Sfx) ) = Sfx, 0)))), A2)
This is an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.
The suffixes MUST be arranged in descending order by length. Otherwise, you might truncate only E4 from a part number whose last letters are RE4.
Last edited by shg; 11-01-2008 at 06:14 PM.
Entia non sunt multiplicanda sine necessitate
Thank You all! Shg, you hit it dead on the nail.Thanks so much. I completely forgot that I have 2 exceptions to the list of part number endings. PARGT & KTT These 2 exceptions do not need to have the "T" eliminated. so for instance in part number MESPT92375KTT it needs to be left alone, and not have the last "T" eliminated. I appreciate your help!
Using way i did it use
Please Login or Register to view this content.
Last edited by martindwilson; 11-05-2008 at 08:39 AM.
Thank you martindwilson!!! This is quite a bit easer to add additional endings along with exceptions to the rule. Thank you! kudos for you!
Hi,
I just came across additional endings that need to be eliminated. In the formula provided above it only eliminates up to 4 characters. I have found I need up to 8. ie ( /2K5G4, /2.7RPOT, TTOOPEAR ) I attempted to add 5,6,7,& 8 but an error popes up "using too many levels of nesting than are allowed" Can you advise a solution?
This is how I added 5,6,7,8.
=Please Login or Register to view this content.
coz of the limit on nested ifs youd have to do it this way (up to 8 characters)
col d must be same length as b
This is a great solution, but my only concern is that I will be analizing 8k-10k line items weekly. This would take too long to go through each row to pull out the differences. Any other ideas?
Perhaps you should discuss the "rules" for how you decide what is an exception. It seems almost random...
once you put the formula in and drag down 10k rows. you only need to paste new data into column a then copy/ paste /special values col b to wherever you want. save the sheet with formulas seperately and just use it as a template.
or you could conditional format say col b with =LEN(a1)<>LEN(b1) then any that had changed length would flag
Last edited by martindwilson; 12-12-2008 at 02:30 PM.
Thank You!Martin gave a great soultion to the exception rule earlier in the thread, which needs only that once I discover an "exception" I add it in the form of
You have a very valid point though.Please Login or Register to view this content.
Thank You Very Much Martin!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks