New Excel Feature Updates Five Text Functions LEN MID SEARCH FIND REPLACE and Compatibility Versions. Microsoft has two important updates in Excel. These updates include five new text functions (LEN, MID, SEARCH, FIND and REPLACE) and introduce compatibility versions to allow for function improvements.
The five text function LEN, MID, SEARCH FIND and REPLACE updated to support unicode surrogate pairs in Excel. These enhancements enhance performance with Unicode characters. Compatibility versions were introduced to allow for function improvements while ensuring consistent results in existing files.
The TEXT function changes how a number is shown by applying formatting codes. It helps make numbers easier to read and allows you to combine them with text or symbols. You can use Excel’s Find and Replace feature to search for a specific number or text in your workbook. You can find the item for reference or replace it with something else.
In this blog post, I will thoroughly explore the new text functions and their compatibility versions. I hope this will help you with your day-to-day work profile. Let’s start.
Table of Contents
What is the Function of LEN and MID in Excel?
The LEN function gives you a number showing how many items are in an object.
Formula for LEN function is given below:
=LEN(cell) in the formula bar, then press Enter
New Excel Feature Updates Five Text Functions LEN MID SEARCH FIND REPLACE and Compatibility Versions
Based on user feedback, Microsoft is updating the LEN, MID, SEARCH, FIND, and REPLACE functions to support Unicode surrogates. This change addresses a significant issue for international users and those who utilize emojis, as these five functions have been double-counting certain characters.
This inconsistency has been particularly evident when compared to more modern text functions, such as LEFT, RIGHT, TEXTBEFORE, TEXTAFTER, and TEXTSPLIT, which do not have this problem.
This update changes how the LEN, MID, SEARCH, FIND and REPLACE functions work. Now, these functions count each character only once. To keep the existing workbooks unchanged, MS introducing these improvements as Compatibility Version 2.
Introducing Compatibility Versions
Compatibility Versions are to improve functions while ensuring your workbooks calculate correctly. Each workbook has specific compatibility versions.
- Version 1 shows how calculations worked in the past.
- Version 2 includes the updates to the text functions mentioned above.
Note: To change a workbook’s Compatibility Version, select Formulas > Calculation Options > Compatibility Version
The following table will show you the various compatibility version scenarios.
| Use of Compatibility Versions | Description |
|---|---|
| Compatibility Versions For Current Files | All existing spreadsheets will be considered as Version 1 to keep calculations consistent. Once you set a spreadsheet’s version, it cannot be changed without you using the abovementioned formula. |
| Compatibility Versions for New Files | New workbooks will be labelled as Version 1. This will change to version 2 after a rollout period for all Microsoft 365 subscription users. It is expected to be finished by January 2026 for the current channel. The recommended version will be applied to new workbooks. This version is the best choice for most users and situations because it has the latest improvements while being accessible to all Microsoft 365 subscription users. |
| Using Older Versions of Excel | If you work with others using older versions of Excel (before Microsoft 365 and Excel 2024), set the compatibility level to Version 1 for consistency. This ensures workbooks will calculate as usual. |
| Upcoming Compatibility Versions | All versions include changes from previous versions. Version 3 will incorporate the text function changes from Version 2. Compatibility versions improve Excel functions while ensuring the existing files calculate correctly. Each version will include new improvements. |
Availability of Five Text Functions and Compatibility Versions
These functions are available to users in Beta Channel Version 2412 (Build 18317.20000) or a newer version. After the successful preview with the Insiders, Microsoft will roll out these features to the public. Some features may not be available to you, and based on feedback, they will be removed or withdrawn.
Resources
Need Further Assistance or Have Technical Questions?
Join the LinkedIn Page and Telegram group to get the latest step-by-step guides and news updates. Join our Meetup Page to participate in User group meetings. Also, Join the WhatsApp Community to get the latest news on Microsoft Technologies. We are there on Reddit as well.
Author
Anoop C Nair has been Microsoft MVP for 10 consecutive years from 2015 onwards. He is a Workplace Solution Architect with more than 22+ years of experience in Workplace technologies. He is a Blogger, Speaker, and Local User Group Community leader. His primary focus is on Device Management technologies like SCCM and Intune. He writes about technologies like Intune, SCCM, Windows, Cloud PC, Windows, Entra, Microsoft Security, Career, etc.