_xlfn. in excel is a prefix that appears, when a function is not recognized by your Excel version. This is usually happens when you enter a formula with a function which has been introduced in the later or newer versions of Excel, but the file is opened in an older version that does not support that function.
To understand this easily, I’m going to use the function TEXTJOIN which is introduced in the 2019 version but in the Excel 2013 which causes _xlfn. in the current version.
Example of _xlfn. in Excel:
If a formula contains the TEXTJOIN function in the formula and it will display the _xlfn. in the formula bar.
=TEXTJOIN(", ", TRUE, B4:B7)
The above formula will be displayed normally in the newer version of the excel but in the older version the same formula will display _xlfn. with it.
=_xlfn.TEXTJOIN(", ", TRUE, B4:B7)
This means that Excel does not recognize TEXTJOIN in your version.
Other Function That may show XLFN in Older Version of Excel:
|
Function Name |
Function Usage |
Function Usage |
|---|---|---|
|
TEXTJOIN |
Merges text strings with a delimiter |
Excel 2016 |
|
IFS |
Formula to have multiple conditions without nested functions |
Excel 2016 |
|
CONCAT |
Function used to replace CONCATENATE function for merging texts |
Excel 2016 |
|
XLOOKUP |
A powerful alternative Function to VLOOKUP and INDEX-MATCH |
Excel 2019 / 365 |
|
FILTER |
Function to filter an array based on the given criteria |
Excel 2019 / 365 |
|
UNIQUE |
Function to give the unique values in the range of area |
Excel 2019 / 365 |
Fix for _xlfn. error while using TEXTJOIN Function:
- You need to upgrade you excel to the latest version. You can follow the instructions provided in the official documentation from Microsoft.
https://support.microsoft.com/en-us/office/install-office-updates-2ab296f3-7f03-43a2-8e50-46de917611c5- You can enable the compatibility mode in your older version of Excel. Check if the file is in Compatibility Mode (.xls format) and convert it to .xlsx to overcome this issue.
- If the above two didn’t help you, then you need to do the text joining using manual formula.
=B4 & ", " & B5 & ", " & B6 & ", " & B7
The above manual formula will joins all the text with “,” comma delimiter without any function to do it.
That’s it.
