Check whether a substring is contained within a string (no error if not)

Question

Level 1

98 points

Check whether a substring is contained within a string (no error if not)

In Numbers 10.2 I can't seem to find a way to check whether a substring (a single letter, actually) is present within a cell. The FIND and SEARCH functions both return the index of a substring within a string, but return an error if the substring is not found. This won't do for my purposes because my broader requirement is for something like:

if the substring is found in a cell, do some calculation, else do some other calculation, e.g.

if (FIND("X", cell_reference ) > 0, some calculation, 0)

The else case will not work with FIND and SEARCH due to the error returned when the substring is not found. FIND and SEARCH should probably return 0 in those cases, but imagining how things should work is not exactly helpful to me right now.

Can anyone suggest another way to check if a substring is found within a string without failing when it isn't?

Posted on Sep 27, 2020 7:53 PM

Reply

Answer 1

Best reply

SGIII

Level 8

35,330 points

Sep 27, 2020 8:19 PM in response to -Marc__

On way is to use IFERROR, something like this:

=IFERROR(IF(FIND("X",A2),"calc if It has it",""),"calc if it doesn't have it")

Substitute ; for , in the formula if your region uses , as a decimal separator.

SG

Reply

Answer 2

Barry

Level 9

59,867 points

Sep 28, 2020 12:43 AM in response to -Marc__

I've often used SUBSTITUTE for this purpose:

SUBSTITUTE replaces the search string with a null string (a text value with zero length)

If the search string is found, the 'substitute' string is a character shorter than the original, the comparison of their lengths returns TRUE, and If does whatever calculation you place in the "contains x position.

If there is no x in the source string, no substitution takes place, the string remains the same length as the original, the comparison returns FALSE, and IF performs the calculation you place in the "HAS no x" position in the formula.

Note that SUBSTITUTE is case sensitive—a search for "x" will not recognize "X" as matching the search string.

Rgards,

Barry

Reply

Answer 3

Badunit

Level 7

26,044 points

Sep 28, 2020 5:57 AM in response to -Marc__

I think the COUNTMATCHES function is another solution. It will return 0 if not found

=IF(COUNTMATCHES(cell_reference, "X" ) > 0, some calculation, 0)

FIND and SEARCH returning an error instead of 0 was a design decision long before Numbers was around. If Numbers did it differently than Excel, it would be a function that would not import/export or it would throw off the formulas that expect an error vs 0. Of course, I believe COUNTMATCHES does not have a counterpart in Excel so it won't export as a function.

Reply