0

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?

Greg Clearwater
  • 39
  • 2
  • 11
  • 1
    http://stackoverflow.com/questions/8753930/how-can-i-use-regexextract-function-in-google-docs-spreadsheets-to-get-all-occ – gaganshera Apr 23 '17 at 20:07

1 Answers1

0

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.

Ed Nelson
  • 10,015
  • 2
  • 27
  • 29