CODEWITHSUNDEEP
regexgoogle-sheetsspreadsheet
user4671628
user4671628

Reputation:

REGEXMATCH and MATCH don't work when a cell contains a number

I am trying to use formulas to find a row in my google spreadsheet document, however I have got a weird problem.

I am not able to find values when a cell contains a number (without any other characters).

Consider the following case

I have got two values 

A1 - 32323232323
A2 - 323-23232-323

When I use the following formula 

=FILTER(A:E,REGEXMATCH(B:B,"323-23232-323"))

It works fine, it successfully finds A2 value, however when I try to use the following formula 

=FILTER(A:E,REGEXMATCH(B:B,"32323232323"))

It doesn't match any row, and I also tried the following formula 

ADDRESS(MATCH("32323232323",B:B,0),1)

It doesn't work either, it only works when I remove quotes like that 

ADDRESS(MATCH(32323232323,B:B,0),1)

But this doesn't work with REGEXMATCH.

Is there any way I can match numbers using a regex expression (exact number, without wildcards) ?

Thanks

Upvotes: 0

Views: 1521

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18717

=FILTER(A:A,REGEXMATCH(REGEXREPLACE(TO_TEXT(A:A),"-",""), "32323232323"))

  • to get both 323-23232-323 and 32323232323.

=FILTER(A:E,REGEXMATCH(TO_TEXT(B:B),"32323232323"))

  • to get number 32323232323.

Notes:

  • Converting to_text is a key here.
  • Change columns to yours.

Upvotes: 3

Related Questions