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.

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 M365 subscription users (~mid-late 2025).

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.

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.