Case-insensitive
=IF(REGEXMATCH(LOWER(A2),"paid"),"Complete","Open")Matches paid, Paid and PAID.
PASTE-READY RECIPE
If a Cell Contains Text, Return Another Value in Google Sheets
Return a label when a cell contains a word or phrase, including partial matches.
QUICK ANSWER
The formula and where it fits.
Return a label when a cell contains a word or phrase, including partial matches. Use =IF(REGEXMATCH(A2,"paid"),"Complete","Open"), then replace the sample ranges with the columns in your sheet.
REGEXMATCH checks for a partial text match. IF chooses the returned label.
- Put the source data under one header row.
- Paste the formula into an empty result cell.
- Change the ranges and criteria to match your sheet.
- Check the result against one row you can verify manually.
TRY ITEdit the table.
Edit the table.
Watch the result change.
| A | |
|---|---|
| 1 | Status note |
| 2 | |
| 3 | |
| 4 | |
| 5 |
FORMULA OUTPUTComplete · Open · Complete · OpenRecalculates from the input cells above
VARIATIONS
Use the version that fits your sheet.
Several words
=IF(REGEXMATCH(LOWER(A2),"paid|settled"),"Complete","Open")Treats either word as a match.
Exact cell
=IF(A2="Paid","Complete","Open")Use this when partial matches should not count.
COMMON ERRORS
When the result looks wrong.
Text case does not match
Wrap the source cell in LOWER and write the search text in lowercase.
A blank cell returns Open
Add IF(A2="","",…) around the formula.
QUESTIONS
Answers before you paste.
Text case does not match
Wrap the source cell in LOWER and write the search text in lowercase.
A blank cell returns Open
Add IF(A2="","",…) around the formula.
MAKE IT YOURS
Adjust this formula →Change the ranges, labels or criteria.
Send this recipe to the SheetSteps assistant and describe how your columns are arranged.