ABC Company Product XXXX for Partner QTY
say I wanted to remove the "ABC Company" and "for Partner"
I used this formula :
IF( CONTAINS(Name,"for Partner") , MID(Name,12,99), Name)
but it only results to Product XXXX for Partner QTY . How would i revise my formula to remove the "for Partner" that's in the middle of the text . Note that the chars after the "for Partner" can be longer depending on the record's name.
5 answers
Alright, so here is the exact solution to get what you want using the example you provided:
Right(Left(Name,Find(" for Partner", Name)),
Len(Left(Name,Find(" for Partner", Name)))-
Find("Product",Left(Name,Find(" for Partner", Name)))+1)
& " " &
Right(Name,Len(Name)-(Find(" for Partner", Name)+12))
For this to work everytime, the Name field must contain the phrase " for Partner" once and the QTY must be after the phrase " for Partner".
This returns you "Product XXXX QTY" when your Name field value is "ABC Company Product XXXX for Partner QTY".
The best way I find to build a complex string formula like this is start small. Start by targeting one part of your sentence at a time and build on it. Make your formula first return "ABC Company Product XXXX", then work on it returning you "Product XXXX", then concatenate the last part. (QTY)
