CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formula
ahmed
ahmed

Reputation: 23

Filter/Extract a text from excel sheet having similar values

I have data in the cell in this format saved in A column in Excel.

[Amber,Maria,Marian,Mar]

[Maria]

[Marian,Amber,Maria]

[Maria,Marian]

I want a filter for Maria and Marian.

How can I use some regex formula filtering Maria and Marian separately?

I am using pivot filters and want to use a custom formula there.

contain(A1,"Maria") will return Maria and Marian both.

Upvotes: 2

Views: 165

Answers (2)

JvdV
JvdV

Reputation: 75870

Google Sheets and Excel are two different apps. Benefits of GS is indeed, as you mentioned in your question, the use of regular expressions. Reading your question there are two possibilities that pop into mind:

enter image description here

Formula in B1:

=INDEX(REGEXMATCH(A1:A5,"Maria\b"))

Formula in C1:

=QUERY(A:A,"where A matches '.*Maria\b.*'")

Upvotes: 1

P.b
P.b

Reputation: 11483

=ISNUMBER(SEARCH("Maria,",SUBSTITUTE(A1:A4,"]",","))) shows TRUE if Maria is found and false if it would only be Marian, not Maria. enter image description here

Upvotes: 3

Related Questions