Get First Text Value in a Row: Excel & Sheets

This article explains how to extract the first text value from a row in Excel or Google Sheets, returning the leftmost non-empty, non-numeric cell. You will learn the exact formulas for both applications, how they work, and how to adapt them for different data layouts.

Formula

The core formula for finding the first text value in a row is an array formula that combines INDEX, MATCH, and ISNUMBER (or ISTEXT). In Excel, the formula is:

=INDEX(A1:Z1, MATCH(TRUE, ISTEXT(A1:Z1), 0))

In Google Sheets, the same formula works, but you may need to press Ctrl+Shift+Enter (or Cmd+Shift+Enter on Mac) to enter it as an array formula, depending on your version.

Variables:

• A1:Z1 – the row range to search (adjust to your actual data).
• ISTEXT(A1:Z1) – returns an array of TRUE/FALSE values, TRUE for text cells.
• MATCH(TRUE, ..., 0) – finds the position of the first TRUE in that array.
• INDEX(range, position) – returns the value at that position.

If your data includes numbers stored as text (e.g., “123”), ISTEXT will treat them as text. If you want to exclude those, use a more complex formula with ISNUMBER and NOT.

Worked Example

Assume row 1 has the following values: A1 = 10, B1 = “”, C1 = “Apple”, D1 = 25, E1 = “Banana”.

1. Apply ISTEXT(A1:E1) → {FALSE, FALSE, TRUE, FALSE, TRUE}.
2. MATCH(TRUE, {FALSE, FALSE, TRUE, FALSE, TRUE}, 0) → 3 (the position of the first TRUE).
3. INDEX(A1:E1, 3) → “Apple”.

So the formula returns “Apple”, which is the first text value in the row.

When to Use This Formula

You need this when your data has a mix of numbers, blanks, and text, and you want to pull out the first descriptive label or name. Common scenarios include:

• Extracting the first non-numeric entry from a row of survey responses.
• Finding the first category name in a row where some columns are blank.
• Cleaning up imported data where the first text column varies.

It is especially useful when the position of the first text is not fixed.

How to Choose the Right Formula

Decide between ISTEXT and ISNUMBER based on what you define as “text”:

• If you want any non-numeric entry (including errors, dates, or logical values), use ISTEXT – it only returns TRUE for actual text strings.
• If you want the first cell that is not a number, use =INDEX(A1:Z1, MATCH(TRUE, INDEX(ISNUMBER(A1:Z1)=FALSE, 0), 0)) – this treats blanks as non-numbers too, which might not be desired.
• If you want the first non-empty cell (text or number), use =INDEX(A1:Z1, MATCH(TRUE, INDEX(A1:Z1<>"", 0), 0)).

For most cases, ISTEXT is the safest because it ignores blanks and numbers.

Common Mistakes and Pitfalls

Several frequent errors occur when using this formula:

• Forgetting to enter as an array formula in older Excel – you must press Ctrl+Shift+Enter, otherwise it returns a #VALUE! error.
• Using a range that doesn’t match the row – ensure the range covers all cells you want to search.
• Confusing ISTEXT with ISNUMBER – ISTEXT returns FALSE for numbers, but also for dates (since dates are numbers in Excel).
• Not accounting for leading/trailing spaces – text with spaces is still text, but may not be visible. Use TRIM if needed.

Also, note that MATCH with a 0 (exact match) is case-insensitive and ignores wildcards when the lookup value is TRUE.

Limitations and Alternatives

The formula works well for a single row, but has limitations:

• It only returns the first text value, not all text values. To get all, you’d need a different approach (e.g., FILTER function in Excel 365 or Google Sheets).
• It doesn’t skip text values that are formulas returning empty strings – those are considered text.
• If the row contains no text, the formula returns #N/A. You can wrap it with IFERROR to return a custom message.

Alternatives include using LOOKUP with a search for text, but that’s more complex. In Excel 365, you can use =TAKE(FILTER(A1:Z1, ISTEXT(A1:Z1)),1) to get the first text value dynamically.

Frequently Asked Questions

How do I get the first non-empty cell in a row?

Use =INDEX(A1:Z1, MATCH(TRUE, INDEX(A1:Z1<>"", 0), 0)). This returns the first cell that is not blank, regardless of whether it’s text or number.

Why does my formula return #VALUE!?

This usually happens when you don’t enter the formula as an array formula in Excel versions prior to 365. Select the cell, press F2, then Ctrl+Shift+Enter. In Google Sheets, it should work automatically, but if not, use ARRAYFORMULA.

How do I get the first text value from a row but skip cells with formulas that return “”?

You can use =INDEX(A1:Z1, MATCH(TRUE, INDEX((A1:Z1<>"")*(ISTEXT(A1:Z1)), 0), 0)) – this checks both that the cell is not empty and that it is text. However, this still considers a formula returning “” as empty, which is good.

Can I use this formula to get the first text from a column instead of a row?

Yes, just change the range from A1:Z1 to A1:A100 (or however long your column is). The formula works identically for columns.

What if my row has no text at all?

The formula returns #N/A. To avoid that, use =IFERROR(INDEX(...), "No text found") to display a friendly message.

Does this formula work in Google Sheets?

Yes, it does. In Google Sheets, you may need to wrap it with ARRAYFORMULA if it doesn’t automatically treat it as an array, but modern versions handle it fine.

Related

• ChatGPT Desktop App for Windows: Is It Free?
• Circumference Formula Calculator: Find a Circle’s Perimeter
• Underrated Movie Soundtracks Worth Hearing Again

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2