I am trying to create formulae that I've never used before and I'm having an issue. I have the following string as a result of my VLOOKUP:
SONIC (325.2 – 3285.1m);DIL (328.6 – 3284.5m);C-ANAL (10.0 – 200.0m);PERF (3169.9 – 3246.1m);COMP (3137.0 – 3175.0m);PA (10.0 – 3260.4m);TNL (3136.4 – 3272.6m);EPS (10.0 – 3156.0m);PERF (3169.9 – 3226.9m);COMP (3080.0 – 3120.0m);PA (10.0 – 410.0m);
I am trying to have the one (result cell) only show me the "PERF" part of the string (and what's within the brackets). I can only get it to show me the 1st instance by using REGEXEXTRACT and have also tried splitting with no positive results; as you can see there are multiple instances (up to 5 in a row).
Can anyone assist?
I am not sure I understand exactly what you want to return, but you can try this query. It splits the values at ';' and transposes them into a column and queries that column for contains 'PERF'. The join then puts them back together in a single cell separated by ';'.
=JOIN(";",query(transpose(split(A1,";")),"select * where Col1 contains 'PERF'"))
You may need to replace the ','s with ';'s depending on your regional settings.