Improving five Excel text functions: LEN, MID, SEARCH, FIND, and REPLACE + Compatibility Versions

 

Hi, Microsoft 365 Insiders! I'm Jake Armstrong, a Product Manager on the Excel team. I'm thrilled to share some improvements to five of our text functions (LEN, MID, SEARCH, FIND, and REPLACE) that will make them work better with Unicode characters – plus, introduce Compatibility Versions, which will allow us to improve functions without changing the results in existing files. Try these improvements here.

Improving our text functions

Based on your feedback, we are updating the LEN, MID, SEARCH, FIND, and REPLACE functions to support Unicode surrogates. This has been a major pain point for international users and those who use emojis, because these five functions have been double-counting certain characters. This was inconsistent with our more modern text functions, which did not have this issue (including LEFT, RIGHT, TEXTBEFORE, TEXTAFTER and TEXTSPLIT).

Below you can see the existing behavior where LEN (🤔) = 2. That’s a bit confusing! 🤔

 

 

With this update, the LEN, MID, SEARCH, FIND, and REPLACE functions now count each character once, no matter what that character is.

To ensure that calculations in your existing workbooks don’t change, these improvements are being rolled out as Compatibility Version 2. Read on to learn more.

Introducing Compatibility Versions

To release improvements to functions while ensuring that workbooks continue to calculate as expected, we are introducing a new feature called Compatibility Versions.

Compatibility Versions are set per workbook. Version 1 reflects historical calculation behavior while Version 2 contains the improvements to text functions described above.

To  change a workbook’s Compatibility Version, select Formulas > Calculation Options > Compatibility Version

Compatibility Versions for existing files

Existing spreadsheets will be set to Version 1, so their calculations will remain consistent. Once a spreadsheet’s version has been set, it will not change unless you change it in through the menu described above.

Compatibility Versions for new files

As of today, new workbooks will be set to Version 1, but this will change to Version 2 after a transition period while it rolls out to all Microsoft 365 subscription users (January 2026 for Current Channel).

The version that is currently being set on new workbooks is called the Recommended Version. It should be considered the best option for most users and scenarios, because it will have the most improvements, yet be accessible by all Microsoft 365 subscription users.

See this help document for details on each version and the current Recommended Version: Compatibility Versions - Microsoft Support

Working with older versions of Excel

If you collaborate with others who are using non-Microsoft 365 Excel versions that predate this functionality (Excel 2024 and earlier), you can set the compatibility level to Version 1 to ensure consistency. Workbooks set to Version 1 will continue to calculate as they always have.

If a user opens a spreadsheet set to a version they do not have access to that contains functions that have changed, a warning will be shown indicating that calculation results may differ. Otherwise, the file will behave normally.

Future Compatibility Versions

Versions are cumulative, so include all changes from prior versions. For example, a future Version 3 will include the text function changes introduced by Version 2.

We’re excited to use Compatibility Versions to continue improving  Excel functions, while ensuring that your existing files continue to calculate consistently. You can expect each Compatibility Version to contain a batch of new improvements and will be detailed in this help document: Compatibility Versions - Microsoft Support

Availability

These functions are currently available to Beta Channel users running Version 2412 (Build 18317.20000) or later. Try these improvements here.

 

Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.

Feedback

We are excited for you to experience these improvements, and we believe they will significantly enhance your productivity and experience with Excel!

 

We want to hear from you!

• If you have any feedback or suggestions, you can submit them by clicking Help > Feedback in Excel. 
• You can also submit new ideas or vote for other ideas via Microsoft Feedback.

 

Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!