Find values string inside a string on google sheets

Question

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

what do you really want ? can you give an example output ? the text is a bit confusing — nabais
– nabais, Commented Oct 20, 2020 at 13:06
"...sometimes the information doesn't come on this way, it may change and also have other information". This is way too vague. What do you mean? Please do not explain in words. Please read how to and share a test sheet with expected results so as you can be easier helped. — marikamitsos
– marikamitsos, Commented Oct 20, 2020 at 13:36
Thanks, I'll created and shate the test sheet, thanks for the informaiton — Tayzer Damasceno
– Tayzer Damasceno, Commented Oct 20, 2020 at 13:40
Perhaps you are looking for Text to Column behaviour? Splitting that string at each comma. support.google.com/docs/answer/6325535 — Aerials
– Aerials, Commented Oct 20, 2020 at 13:52

marikamitsos · Accepted Answer · 2020-10-20 14:23:05Z

1

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

answered Oct 20, 2020 at 14:23

marikamitsos

10.6k23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tayzer Damasceno Over a year ago

is exactly what I would like to use, thank you so much for your time and effort

Collectives™ on Stack Overflow

Find values string inside a string on google sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related