Function Problems

How to Fix XLOOKUP and LEFT Formula Results Returning Text in Excel

Adam Davis Sep 27, 2026 869 views

Question details

The user needs to retrieve averages using XLOOKUP and display them as whole numbers without converting them to text, ensuring subsequent SUM formulas calculate properly.

How to Fix XLOOKUP and LEFT Formula Results Returning Text in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating and summing numeric values, such as bowling averages, retrieved via XLOOKUP.
Observed behavior: Using the LEFT function to extract the whole number converts the numeric value to a text string, which causes SUM formulas referencing those cells to return zero.

Before you start

Select the cells containing your formula results and ensure their cell format is set to 'General' or 'Number' rather than 'Text' before modifying your functions.

Solution 1Recommended

Use ROUND or ROUNDDOWN Instead of LEFT

The LEFT function is designed for text manipulation and automatically converts numbers to text strings. Using math functions like ROUND or ROUNDDOWN keeps the data numeric.

When you use text functions like LEFT, RIGHT, or MID on a number, Excel automatically changes the result into text. Mathematical functions like SUM ignore text values, resulting in a zero output. To fix this, you should replace the text function with a numeric rounding function.

Select the formula cell

Click on the cell containing your current XLOOKUP and LEFT formula.

Apply the ROUND function

Replace the LEFT function wrapper with ROUND to round to the nearest whole number. For example: =ROUND(XLOOKUP(...), 0). This rounds values like 130.55 up to 131.

Use ROUNDDOWN for truncation

If you specifically need to truncate the decimal without rounding up (to mimic the exact behavior of LEFT), use the ROUNDDOWN function instead: =ROUNDDOWN(XLOOKUP(...), 0). This forces 130.55 down to 130.

Fill the formula down

Press Enter, then click and drag the fill handle at the bottom right corner of the cell to apply the updated formula to your entire column.

Calculation Fixed: Because the results are now true numeric values, your SUM formula at the bottom of the column will correctly calculate the total.

Solution 2

Wrap the Formula in the VALUE Function

If your specific workflow requires extracting characters using LEFT, you can force the resulting text back into a numeric value using the VALUE function.

Fix Formula Errors Instantly with WPS Spreadsheet

WPS Office offers a powerful, highly compatible Spreadsheet tool that perfectly supports XLOOKUP, ROUND, and SUM functions. Easily troubleshoot formula errors, format data, and handle complex calculations without compatibility issues.

1. Open your workbook: Launch WPS Spreadsheet and open the .xlsx file containing your faulty SUM calculation.
2. Locate the error: Find the cell using the LEFT function that is converting your numeric XLOOKUP results into text.
3. Update the formula: Modify the formula to =ROUNDDOWN(XLOOKUP(...), 0) to truncate decimals while keeping the data format numeric.
4. Verify the sum: Highlight the updated cells and instantly verify the correct numeric total in the status bar at the bottom right of the screen.

100% compatible with Microsoft Excel (.xlsx) formats and functionsPerfectly supports advanced lookup and math functions like XLOOKUP and ROUNDDOWNBuilt-in error checking for quick formula troubleshootingFree, lightweight, and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUM formula return 0 when referencing valid numbers?

This happens when numbers are accidentally stored as text. Using text-extraction functions like LEFT, RIGHT, or MID converts numeric values into text strings. The SUM function ignores text strings, resulting in a calculation of zero.

What is the exact difference between ROUND and ROUNDDOWN?

ROUND will round a number to the nearest specified decimal place (e.g., 130.55 becomes 131 if set to 0 decimals). ROUNDDOWN always rounds towards zero, effectively truncating or cutting off the decimal part (e.g., 130.55 becomes 130).

How can I easily tell if my numbers are stored as text?

By default, text is left-aligned in a cell, while true numeric values are right-aligned. Additionally, Excel or WPS Spreadsheet may display a small green triangle in the upper-left corner of the cell, warning you that there is a 'Number Stored as Text'.

Excel & Data Problems