[Solved] If "specific text" exists, then 1, else 0

[alienrobotghost]

I need a formula that will check if the text in a cell contains the text "bio", anywhere in the text, and return a 1 if it does, and a 0 if it doesn't


is this something that is possible?

[robleyd]

Yes. One way would be to use the FIND() function wrapped in an IF().

[alienrobotghost]

Yes. One way would be to use the FIND() function wrapped in an IF().

I tried that... and failed miserably... lol

[robleyd]

What exactly did you try? And what was the result?

Edit: You'll need an ISERR() in there as well.

[Lupp]

Assume column $A contains the strings. You will get the numeric results you mentioned to indicate the presence of the substring "bio" by

Code: Select all

= 0 + ISNUMBER(FIND("bio"; $A1))

The

Code: Select all

0+

makes Calc format the result cell to "Number / General" automatically.
If you prefer to get the results formatted automatically as Boolean (logical) use

Code: Select all

= ISNUMBER(FIND("bio"; $A1))

If you want a case insensitive formula use

Code: Select all

= 0 + ISNUMBER(FIND(UPPER(substring); UPPER($A1)))

and respectively.

[MrProgrammer]

I need a formula that will check if the text in a cell contains the text "bio", anywhere in the text, and return a 1 if it does, and a 0 if it doesn't

=N(SUBSTITUTE(cell;"bio";"")<>cell)

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[alienrobotghost]

You guys are the best!!!