formula remove text /keep text in (...)
Hi Experts,
I would like to keep only the text which is in brackets below.
[clear onix/ftwr white/solar yellow (FY6226), orbit indigo/ftwr white/tactile blue (FY7852), ftwr white/solar red/iron met. (FY6218), core black/core black/dark grey (FY6222)]
Result in the new column should be FY6226, FY7852, FY7852, FY6222
I start with this formula, but than I miss the art numbers at the end and not sure how to solve it.
=IFERROR(MID(Articles@row, FIND("(", Articles@row) + 1, FIND(")", Articles@row) - FIND("(", Articles@row) - 1), "")
Thanks for your help!
Christine
Best Answer
Answers
-
Here is my suggestion for pulling the first one...
=IFERROR(MID(Articles@row, FIND("(", Articles@row) + 1, FIND(")", Articles@row) - (FIND("(", Articles@row) + 1)), "")
To pull all of them I would suggest 4 additional helper columns plus the column for the final string. The extra 4 columns can be hidden after setting everything up to help the sheet look clean. While technically this could be done in a single formula, it will get very big and bulky and convoluted and almost impossible to trouble shoot.
The reason for the 4 helper columns: We can have one for each of the art numbers you want to pull and use cell references in the FIND functions to essentially replicate the formula pulling the first one but telling the FIND functions to only start searching AFTER the previous one.
So that means the second formula would look something like this...
=IFERROR(MID(Articles@row, FIND("(", Articles@row, FIND([1st Helper]@row + 2)) + 1, FIND(")", Articles@row, FIND([1st Helper]@row + 2)) - (FIND("(", Articles@row, FIND([1st Helper]@row + 2)) + 1)), "")
Third formula:
=IFERROR(MID(Articles@row, FIND("(", Articles@row, FIND([2nd Helper]@row + 2)) + 1, FIND(")", Articles@row, FIND([2nd Helper]@row + 2)) - (FIND("(", Articles@row, FIND([2nd Helper]@row + 2)) + 1)), "")
And the fourth formula:
=IFERROR(MID(Articles@row, FIND("(", Articles@row, FIND([3rd Helper]@row + 2)) + 1, FIND(")", Articles@row, FIND([3rd Helper]@row + 2)) - (FIND("(", Articles@row, FIND([3rd Helper]@row + 2)) + 1)), "")
And last but not least the formula to bring them all together into a single string...
=JOIN(COLLECT([1st Helper]@row:[4th Helper]@row, [1st Helper]@row:[4th Helper]@row, @cell <> ""), ", ")
-
Thank @Paul Newcome ,
I tried it, but seems not to work. I followed your header names and copied the exact formula from above.
1st formula is working, but for the rest, I get below errors
Any idea?
2nd formula:
=IFERROR(MID(Articles@row, FIND("(", Articles@row, FIND([1st Helper]@row + 2)) + 1, FIND(")", Articles@row, FIND([1st Helper]@row + 2)) - (FIND("(", Articles@row, FIND([1st Helper]@row + 2)) + 1)), "")
3rd:
=IFERROR(MID(Articles@row, FIND("(", Articles@row, FIND([2nd Helper]@row + 2)) + 1, FIND(")", Articles@row, FIND([2nd Helper]@row + 2)) - (FIND("(", Articles@row, FIND([2nd Helper]@row + 2)) + 1)), "")
4th
=JOIN(COLLECT([1st Helper]@row:[4th Helper]@row, [1st Helper]@row:[4th Helper]@row, @cell <> ""), ", ")
Thanks a lot!
-
Sorry about that. My fingers and my brain weren't on the same wavelength.
=IFERROR(MID(Articles@row, FIND("(", Articles@row, FIND([1st Helper]@row, Articles@row) + 2) + 1, FIND(")", Articles@row, FIND([1st Helper]@row, Articles@row) + 2) - (FIND("(", Articles@row, FIND([1st Helper]@row, Articles@row) + 2) + 1)), "")
Just change 1st to 2nd to 3rd as needed and then the final formula should be working.
-
Thanks @Paul Newcome ,
but the result is still not correct. I guess the calculation of the 2nd helper is not right?!
-
I know I have helped someone else through something very similar here in the Community before. Let me dig through my notes and get back to you.
-
Will there always be four sets, or could it be anywhere from 1 to 4?
-
could it be anywhere from 1 to 5
-
Ok. Give these a try. The Final formula is still going to be the JOIN/COLLECT.
1st Helper:
=MID(Articles@row, FIND("(", Articles@row) + 1, FIND(")", Articles@row) - (FIND("(", Articles@row) + 1))
2nd Helper:
=IF([1st Helper]@row <> "", IFERROR(MID($Articles@row, FIND("(", $Articles@row, FIND([1st Helper]@row, $Articles@row) + 2) + 1, FIND(")", $Articles@row, FIND([1st Helper]@row, $Articles@row) + 2) - (FIND("(", $Articles@row, FIND([1st Helper]@row, $Articles@row) + 2) + 1)), ""))
3rd Helper:
=IF([2nd Helper]@row <> "", IFERROR(MID($Articles@row, FIND("(", $Articles@row, FIND([2nd Helper]@row, $Articles@row) + 2) + 1, FIND(")", $Articles@row, FIND([2nd Helper]@row, $Articles@row) + 2) - (FIND("(", $Articles@row, FIND([2nd Helper]@row, $Articles@row) + 2) + 1)), ""))
4th Helper:
=IF([3rd Helper]@row <> "", IFERROR(MID($Articles@row, FIND("(", $Articles@row, FIND([3rd Helper]@row, $Articles@row) + 2) + 1, FIND(")", $Articles@row, FIND([3rd Helper]@row, $Articles@row) + 2) - (FIND("(", $Articles@row, FIND([3rd Helper]@row, $Articles@row) + 2) + 1)), ""))
5th Helper:
=IF([4th Helper]@row <> "", IFERROR(MID($Articles@row, FIND("(", $Articles@row, FIND([4th Helper]@row, $Articles@row) + 2) + 1, FIND(")", $Articles@row, FIND([4th Helper]@row, $Articles@row) + 2) - (FIND("(", $Articles@row, FIND([4th Helper]@row, $Articles@row) + 2) + 1)), ""))
-
Hi Paul,
looks still like this with the updated formula.
-
Take the formula in the [2nd Helper] Column. Locate the "+ 2" after the "FIND(")", " (finding the closed parenthesis) and change the 2 to "(LEN([1st Helper]@row) + 1)" like so...
=IF([1st Helper]@row <> "", IFERROR(MID($Articles@row, FIND("(", $Articles@row, FIND([1st Helper]@row, $Articles@row) + 2) + 1, FIND(")", $Articles@row, FIND([1st Helper]@row, $Articles@row) + 2) - (FIND("(", $Articles@row, FIND([1st Helper]@row, $Articles@row) + 2) + 1)), ""))
Bold "2" changes to:
=IF([1st Helper]@row <> "", IFERROR(MID($Articles@row, FIND("(", $Articles@row, FIND([1st Helper]@row, $Articles@row) + 2) + 1, FIND(")", $Articles@row, FIND([1st Helper]@row, $Articles@row) + (LEN([1st Helper]@row) + 1)) - (FIND("(", $Articles@row, FIND([1st Helper]@row, $Articles@row) + 2) + 1)), ""))
You want to change that portion in each of the helper columns to reference the helper column before and make sure the LEN() + 1 is wrapped in parenthesis.
(LEN([1st Helper]@row) + 1)
-
Perfect Paul it's working!
Help Article Resources
Categories
Check out the Formula Handbook template!
