Get first text value with XLOOKUP

To lookup and get the first text value in a range, use the XLOOKUP function with a wildcard character match mode.

From time to time, you want to find and extract the first text value from a range that contains numbers and text values. Use the XLOOKUP function and apply the wildcard character (*) as a lookup value.

Generic formula to get the first text value

Here is the generic formula:

=XLOOKUP("*",range, range,,2)

In the example, we find the first text value in each row. Use the formula in cell G3 and copy the formula down:

=XLOOKUP("*",B3:E3,B3:E3,,2)

Configure the XLOOKUP arguments like this:

  • lookup value is a wildcard (*), that matches one or more text values
  • lookup array: B3:E3
  • return array: B3:E3
  • match mode: 2

Workaround with HLOOKUP

If you are working with the non-Microsoft365 version of Excel, you can use the HLOOKUP function to extract the first cell containing text value.

get first text value HLOOKUP

In the example, configure the HLOOKUP function arguments the following way:

  • lookup value: “*”
  • table array: B3:E3
  • row index num: 1
  • range lookup: 0

Explanation: The lookup value is the same as the XLOOKUP example; using “*” will lookup text values in a selected range. The table array is a range where we find the matching record. The row index number is 1 since we have a range that contains a single row. Finally, set the range lookup argument to 0. Using 0, the HLOOKUP and the VLOOKUP function will use an exact match.

Skip empty strings

Empty strings can cause traffic jams in Excel, so we need to modify the formulas. For example, try to imagine that cell D3 contains three spaces that are invisible by default. Then, in the example below, take a look at the result:

handling empty strings

If you want to ignore empty strings, modify the formulas and add a question mark “?” character before the asterisk “*”.

Formulas with an empty string handling:

=HLOOKUP("?*",B3:E3,1,0)

Result:

formula to manage empty cells

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.