Function Problems

Fix Excel TEXTJOIN Empty Values and the MATCH 255-Character Limit

Jason MillerJason Miller Nov 14, 2026 868 views

Question details

The user needs to troubleshoot unexpected empty values in TEXTJOIN results and compare text longer than 255 characters with MATCH.

Fix Excel TEXTJOIN Empty Values and the MATCH 255-Character Limit
Product
Microsoft Excel 365
Device & OS
not provided
Scenario
The user is combining array values with TEXTJOIN and looking up long text strings in a workbook.
Observed behavior
TEXTJOIN results may retain unexpected empty values and change after formulas are re-entered or the workbook is recalculated. MATCH has a known limitation when its lookup value exceeds 255 characters.
Before you start

Identify a few affected rows and note the formulas and source ranges they use. Check whether apparently empty source cells contain spaces, formulas returning empty strings, or other characters.

Solution 1Recommended

Check TEXTJOIN data and recalculate the workbook

Use these checks to isolate unexpected separators or results that change after recalculation.

1
Check the formula

Select an affected TEXTJOIN cell and inspect the formula bar. Confirm that the second argument is TRUE, as in =TEXTJOIN(", ",TRUE,A2:A10), to ignore empty values.

2
Inspect affected source cells

Select the source cells used by the affected rows and check for spaces or other characters. In a spare cell, use =LEN(A2) to check the length of a suspect value, replacing A2 with the relevant reference.

3
Review calculation settings

Open the Formulas tab, select Calculation Options, and choose Automatic. Then select Calculate Now and compare the affected results.

4
Test in a clean workbook

Copy a small set of affected source values and the relevant formulas into a new workbook. Recalculate it and compare the results, adjusting references as needed.

5
Check for Office updates

In Excel, open File > Account > Update Options and select Update Now if the option is available.

Check TEXTJOIN data and recalculate the workbook
Results may need further investigation: If only certain rows are affected or recalculation changes the result temporarily, the source data or workbook calculation chain may be involved. These checks may not identify a single cause.
Free Microsoft Office alternative

Review Excel Workbooks with WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks to review formulas and data while moving between Office applications.

Open and edit compatible Microsoft Excel XLSX and XLS filesReview spreadsheet data and formulas in a familiar interfaceUse a free, lightweight Office suiteMove existing workbooks between compatible Office applications
microsoft office alternative - wps office

Frequently Asked Questions

Why does TEXTJOIN still show gaps when ignore_empty is TRUE?

The source may contain spaces or other characters that look blank. Check a suspect cell with =LEN(A2) and inspect the formula bar. Also confirm that TRUE is the second argument of TEXTJOIN.

How do I make Excel recalculate formulas automatically?

Open the Formulas tab, select Calculation Options, and choose Automatic. Use Calculate Now on the same tab to recalculate the workbook.

How can I match a text value longer than 255 characters?

Compare the lookup range with the value first, then match the Boolean result. For example, use =MATCH(TRUE,INDEX(A2:A100=D2,0),0). The result is the position within the lookup range.

Can I use LEFT to work around the MATCH limit?

You can compare shortened prefixes using LEFT, but two different strings may have the same prefix. Use a full-text comparison if the result must be an exact match.

What should I do if TEXTJOIN changes after recalculation?

Test the affected rows and formulas in a clean workbook, check the source cells and calculation settings, and update Office through File > Account > Update Options if available.