LinkBack URL
About LinkBacks
I have a database of design but can have up to 8 different suffixes depending who they are for but basically its the same item
I need a way of remove certain characters from the end of string as per the supplied sample
I have a database of design but can have up to 8 different suffixes depending who they are for but basically its the same item
I need a way of remove certain characters from the end of string as per the supplied sample
In A2-A6, you deleted all alpha chars after the last digit. A7, you deleted only 3 of 5, A8, 2 of 4, so what's the rule? Do you have a list of suffixes?
Ben Van Johnson
The suffixes are CL ,AS , ZNK , MHT , HMT , TE , LE and possibly more over time
Do you have a list of core codes (without these suffixes)?
Excel needs something to use to establish a pattern. Your sample dataset has only two patterns, and there is nothing there that can readily be used to tell Excel what to do.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I have a list of 3500 core codes with more been added weekly
Basically I need a formula or macro that will look at the code and if it ends with one of the suffixes remove the suffix
If the suffixes were the same length I have a working formula because they are 2 or 3 characters in length this doesn’t work
OK - so the only way you will be able to achieve this is to have a dynamic list of suffixes that can be added to. You cannot do it without a reference array. Do you understand this?
This means that you will need to work with a table containing the suffixes that can be referenced.
Just for your example, put this on B2 and copied down and Im using helper table:
=IFERROR(SUBSTITUTE(A2,MID(A2,IFERROR(LOOKUP(2*15,SEARCH($F$2:$F$8,SUBSTITUTE(SUBSTITUTE(A2,",","")," ",""))),""),100),""),A2)
Many thanks works perfectly ,just what I needed
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Many thanks when I try to expand the table of suffixes to include to cell f10 it doesn't work
I was hoping to just change $F$2:$F$8 to $F$2:$F$10
Can you let me know where I am going wrong
Should work as long as those cells in row 9 and 10 are not blank.
This is why I suggested that you need something that will adjust dynamically like a table.
In the attached you will find that I've converted your lookup list to a table - this will now adjust when you add new codes to it, and the formula will adapt automatically, too.
Last edited by AliGW; 07-11-2019 at 05:40 AM.
Just posting again in case you missed the attachment I added to my last post.
I have also spent some time with your duplicate thread, so just posting my solution in this thread.
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
In B2 Cell
Please Login or Register to view this content.
=RemoveSuffix(A2,Table1[Suffixes])
Drag the B2 cell formula down.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks