Ask Your Question

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

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

James Evans gravatar image

updated 2014-01-12 04:27:53 +0200

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 flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-18 14:23:54.365045


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

bencomp gravatar imagebencomp ( 2014-01-12 19:30:42 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-01-12 04:43:26 +0200

James Evans gravatar image

add: &"." instead of just .

Code requested was:

=MATCH(C2&".*", $b$2:$b$10000, 0)
edit flag offensive delete link more

answered 2014-01-12 19:33:06 +0200

bencomp gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


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

Seen: 2,043 times

Last updated: Jan 12 '14