How to extract text from cells with distinct

user_3283685 0
Viewed 48

I tried different approaches to accomplish what I am looking for and it might not be possible with how I have formated my data but I will try to explain it to see if there is a way.

My origin of data looks like the following:

Case HELP
100 HELP-01 HELP-02
101 HELP-01
102
103 HELP-03

What I want is to be able to extract the HELP-* into another column without duplicate values and one after another. The result I am looking for is from the above table been able to have this:

HELP
HELP-01
HELP-02
HELP-03

Is there a way to do this in Google Sheets?

Thank you,

google-sheets-formula formula google-sheets
2 Answers

Try this in Google Sheets

=sort(array_constrain(unique(flatten(B2:C)),counta(B2:C),1))
user_150193800

Alternatively you can use:

=UNIQUE(QUERY(FLATTEN(B2:C),"where Col1 like 'HELP-%'"))

The QUERY() will now only return those values that start with 'HELP-' in the case you might have other string-values.

A littel more specific even, could be to use:

=UNIQUE(QUERY(FLATTEN(B2:C),"where Col1 matches 'HELP-\d+'"))

Where 'matches' will now use the regular expression to only return values that start with 'HELP-' but end with any 1+ digits.

user_97581940
Related