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-Microsoft 365 version of Excel, you can use the HLOOKUP function to extract the first cell containing text value.
In the example, configure the HLOOKUP function arguments in 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:
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:
