Ask Your Question

# [Calc] Partial word searches, using cell references and regular expressions. [closed]

So I know I can use regular expressions to do something like :

=MATCH("hello.*", $b$2:$b$10000, 0)


But if i have two collums, b and c, and I want to find if the word in the cell at c is included, whole or partially, in list b. For example:

B            C
Helpxo       Help
12345-075    12345
123          123


So I want to be able to put a formula in D that gives positive matches for all of those examples. I tried:

=MATCH(C2.*, $b$2:$b$10000, 0)


But, it didn't work.

EDIT: Found it here's the code:

=MATCH(C2&".*", $b$2:$b$10000, 0)

edit retag reopen merge delete

## Comments

If you mark your answer 'correct', others will find the answer, there's no need to include it in the question :)

## 2 Answers

Sort by » oldest newest most voted

add: &"." instead of just .

Code requested was:

=MATCH(C2&".*", $b$2:$b$10000, 0)

more The first parameter you give to the MATCH function is a regular expression, expressed as a string. You can express it using the literal form (e.g. "hello.*" = "hello" + zero or more occurrences of any character), or as a reference to a cell containing a regular expression (e.g. C2), or as a combination (e.g. a concatenation: C2&".*" = the value of C2 followed by zero or more occurrences of any character). This will only match when the sought string is at the beginning of the cell.

more

## Stats

Asked: 2014-01-12 04:08:56 +0200

Seen: 1,758 times

Last updated: Jan 12 '14