Fix Excel TEXTJOIN Empty Values and the MATCH 255-Character Limit
Question details
The user needs to troubleshoot unexpected empty values in TEXTJOIN results and compare text longer than 255 characters with MATCH.

- 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.
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.
Check TEXTJOIN data and recalculate the workbook
Use these checks to isolate unexpected separators or results that change after recalculation.
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.
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.
Open the Formulas tab, select Calculation Options, and choose Automatic. Then select Calculate Now and compare the affected results.
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.
In Excel, open File > Account > Update Options and select Update Now if the option is available.

Match long text with a Boolean comparison
Use this alternative when MATCH cannot directly compare a lookup value longer than 255 characters.
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.

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.




