Function Problems

Return a Value Based on Which Excel Cells Contain Text

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

Question details

Return different values depending on which of cells A2, B2, and C2 contain text.

Return a Value Based on Which Excel Cells Contain Text
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs to classify each row according to the text values in three cells.
Observed behavior
The desired formula returns x, y, or z for specified text patterns and a blank result for other patterns.
Before you start

Check that A2, B2, and C2 contain the values you want to test, and choose an empty result cell such as D2. The formula treats numbers as non-text, while a formula returning an empty string is treated as text.

Solution 1Recommended

Use nested IF and ISTEXT to classify the cells

Use this formula to check the three specified text patterns and return a corresponding value.

1
Select the result cell

In Excel, select D2 or another empty cell where you want the result to appear.

2
Enter the formula

Paste =IF(AND(ISTEXT(A2),ISTEXT(B2),ISTEXT(C2)),"x",IF(AND(ISTEXT(A2),NOT(ISTEXT(B2)),ISTEXT(C2)),"y",IF(AND(NOT(ISTEXT(A2)),NOT(ISTEXT(B2)),ISTEXT(C2)),"z",""))) into the formula bar and press Enter.

3
Apply it to other rows

Select D2 and drag the fill handle down column D to copy the formula. The cell references will adjust for each row.

Use nested IF and ISTEXT to classify the cells
How the results are assigned: The formula returns x when all three cells contain text, y when A2 and C2 contain text but B2 does not, and z when only C2 contains text. Other patterns return an empty string. NOT(ISTEXT(...)) includes numbers and empty cells, not just empty cells.
Work with spreadsheet formulas

Classify text patterns in WPS Spreadsheets

Use the same nested IF and ISTEXT formula in WPS Spreadsheets to return a value for each text pattern.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Select a result cell: Select D2 or another empty cell beside the values in A2, B2, and C2.
  3. 3. Enter the formula: In the formula bar, enter =IF(AND(ISTEXT(A2),ISTEXT(B2),ISTEXT(C2)),"x",IF(AND(ISTEXT(A2),NOT(ISTEXT(B2)),ISTEXT(C2)),"y",IF(AND(NOT(ISTEXT(A2)),NOT(ISTEXT(B2)),ISTEXT(C2)),"z",""))) and press Enter.
  4. 4. Fill down and save: Drag the result cell's fill handle down to apply the formula to additional rows, then use Save to save the workbook.
Test multiple cells with familiar IF and ISTEXT functionsOpen and edit Microsoft Excel XLS and XLSX filesCopy the formula down a column to classify multiple rowsUse a familiar spreadsheet interface for formula editing
microsoft office alternative - wps office

Frequently Asked Questions

What happens if one of the cells contains a number?

ISTEXT returns FALSE for a number, so the formula treats it as non-text. For example, a number in B2 can produce y if A2 and C2 contain text.

Does ISTEXT treat a formula returning an empty string as text?

Yes. A cell whose formula returns "" is treated as text by ISTEXT, even though it appears empty. A truly empty cell is treated as non-text.

How can I use the formula for multiple rows?

Enter the formula in D2, select that cell, and drag its fill handle down. Relative references such as A2 will change to match each row.

Can Microsoft 365 calculate the results for a whole range at once?

Yes. In a version of Excel that supports MAP and LAMBDA, you can apply the tests to corresponding ranges such as A2:A7, B2:B7, and C2:C7 in one dynamic-array formula. Enter it in a cell with enough empty space below for the results.