CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Tayzer Damasceno
Tayzer Damasceno

Reputation: 302

Find values string inside a string on google sheets

I'm having some difficulty to extract the information inside the string.

For example, I would like to have the entire line where I have "MODERATE: harm/physical violence/physical abuse [220]", actually the line where I have the code [220].

An example, I have this text on the column BS

"NONE: sexual/anatomy/nudity [152],
NONE: sexual/sex acts [100],
NONE: language/profanity [400],
NONE: harm/physical violence/moment of death [227],
MODERATE: harm/physical violence/physical abuse [220],
NONE: modifier/intent/edsa [902],
NONE: modifier/video games [963],
NONE: modifier/music video [960],
NONE: modifier/news [961]"

and I would like to take out those parts separeted with the following codes (not the entire information):

NONE: sexual/anatomy/nudity [152]

NONE: sexual/sex acts [100]

NONE: language/profanity [400]

MODERATE: harm/physical violence/physical abuse [220]

But I need to be aware cause sometimes the information doesnt come on this way, it may change and also have other information.

Here is a test sheet with some information and the expected result

https://docs.google.com/spreadsheets/d/16fA2e3e6fm-xwkFek28pJ2VHL4F_fiYWvkIVmDuFNEo/edit?usp=sharing

enter image description here

Upvotes: 0

Views: 78

Answers (1)

marikamitsos
marikamitsos

Reputation: 10573

Please see if the following meets your needs

In cell D4 place the following formula

=ArrayFormula(IFERROR(if(LEN(A4:A),IF(--REGEXEXTRACT(A4:A, ""&D2&"")=D2,REGEXEXTRACT(A4:A, ".*\["&D2&"\]")),"")))

Then adjust for the rest cells E4, F4, etc accordingly

Upvotes: 1

Related Questions