https://techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066 * [MicrosoftL] Microsoft Tech Community Home Community Hubs Community Hubs * Community Hubs Home * Products * Special Topics * Video Hub Close Products (68) Special Topics (42) Video Hub (876) Most Active Hubs Microsoft Teams Microsoft Excel Windows Security, Compliance and Identity Office 365 SharePoint Windows Server Azure Exchange Microsoft 365 Microsoft Edge Insider .NET Sharing best practices for building any app with .NET. Microsoft FastTrack Best practices and the latest news on Microsoft FastTrack Microsoft Viva The employee experience platform to help people thrive at work Most Active Hubs ITOps Talk Core Infrastructure and Security Microsoft Learn Education Sector Microsoft 365 PnP AI and Machine Learning Microsoft Mechanics Healthcare and Life Sciences Small and Medium Business Public Sector Internet of Things (IoT) Azure Partner Community Expand your Azure partner-to-partner network Microsoft Tech Talks Bringing IT Pros together through In-Person & Virtual events MVP Award Program Find out more about the Microsoft MVP Award Program. Video Hub Azure Exchange Microsoft 365 Microsoft 365 Business Microsoft 365 Enterprise Microsoft Edge Microsoft Outlook Microsoft Teams Security SharePoint Windows Browse All Community Hubs Blogs Blogs Events Events * Events Home * Microsoft Ignite * Microsoft Build * Community Events Microsoft Learn Microsoft Learn * Home * Community * Blog * Azure * Dynamics 365 * Microsoft 365 * Security, Compliance & Identity * Power Platform * Github * Teams * .NET Lounge Lounge * 899K Members * 4,291 Online * 253K Discussions Search [Search] [ ] [ ] [ ] [ ] [ ] cancel Turn on suggestions Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for Show only | Search instead for Did you mean: Sign In Sign In [Search] [ ] [ ] [ ] [ ] [ ] cancel Turn on suggestions Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for Show only | Search instead for Did you mean: Home * Home * * Microsoft Excel * * Excel Blog * * Announcing New Text and Array Functions * Back to Blog * Newer Article * Older Article Announcing New Text and Array Functions * Subscribe to RSS Feed * * Mark as New * Mark as Read * * Bookmark * Subscribe * * Email to a Friend * * Printer Friendly Page * Report Inappropriate Content By JoeMcDaid Joe McDaid Published Mar 16 2022 11:41 AM 136K Views JoeMcDaid JoeMcDaid Microsoft Mar 16 2022 11:41 AM Announcing New Text and Array Functions Mar 16 2022 11:41 AM August 17th 2022 Update We've begun rolling out this feature to subscription users in the Current Channel on all endpoints. This banner will be updated once the feature is fully deployed. I'm thrilled to share with you the availability of 14 new Excel functions designed to help you more easily manipulate text and arrays in your worksheets. Text Manipulation Functions When working with text, a common task to complete is "break apart" text strings using a delimiter. You can already do this with combinations of SEARCH, FIND, LEFT, RIGHT, MID, SUBSTITUTE, and SEQUENCE, but we've heard from many of you that these can be challenging to use. To make it easier to extract the text from the start or end of a cell's contents, we are releasing two functions that simply return everything before or after your selected delimiter. Welcome, TEXTBEFORE and TEXTAFTER! We've also made it easy to "split" text into multiple segments using TEXTSPLIT. Each text segment is then automatically spilled into its own cell through the magic of dynamic arrays. Text 1.gif * TEXTBEFORE - Returns text that's before delimiting characters * TEXTAFTER - Returns text that's after delimiting characters * TEXTSPLIT - Splits text into rows or columns using delimiters Array Manipulation Functions Since the release of dynamic arrays in 2019, we've seen a large increase in the usage of array formulas. To make it easier to build compelling spreadsheets using dynamic arrays, we are releasing a collection of 11 new array manipulation functions. Combining Arrays It can be challenging to combine data, especially when their sources are flexible in size. With VSTACK and HSTACK, you can easily combine dynamic arrays, stacking your data vertically or horizontally. Combining 2.gif * VSTACK - Stacks arrays vertically * HSTACK- Stacks arrays horizontally Shaping Arrays It has been challenging to change the "shape" of data in Excel, especially from arrays to lists and vice versa. If you find yourself with a two-dimensional array that you would like to convert to a simple list, use TOROW and TOCOL to convert a 2D array into a single row or column of data. Using the WRAPROWS and WRAPCOLS functions, do the opposite: create a 2D array of a specified width or height by "wrapping" data to the next line (just like the text in this document) once your chosen width/height limit is reached. Shaping Short 2.gif * TOROW - Returns the array as one row * TOCOL - Returns the array as one column * WRAPROWS - Wraps a row array into a 2D array * WRAPCOLS - Wraps a column array into a 2D array Resizing Arrays Arrays too large? No problem. Enter the TAKE and DROP functions! They enable you to reduce your arrays by specifying the number of rows to keep or remove from the start or end of your array. Similarly, using CHOOSEROWS or CHOOSECOLS, you can pick specific rows or columns out of an array by their index. EXPAND allows you to grow an array to the size of your choice--you just need to provide the new dimensions and a value to fill the extra space with. Resizing Short 1.gif * TAKE - Returns rows or columns from array start or end * DROP - Drops rows or columns from array start or end * CHOOSEROWS - Returns the specified rows from an array * CHOOSECOLS - Returns the specified columns from an array * EXPAND - Expands an array to the specified dimensions Scenarios to try * Use " " (space) as a delimiter with TEXTBEFORE to extract the first name and TEXTAFTER to extract the last name * Use TEXTSPLIT to separate the names into an array with " " (space) as a delimiter When you want to combine two ranges of data: * Use VSTACK to combine two ranges of data vertically * Use HSTACK to combine two ranges horizontally Availability These functions are currently available to users running Beta Channel, Version 2203 (Build 15104.20004) or later on Windows and Version 16.60 (Build 22030400) or later on Mac. Don't have it yet? It's probably us, not you. 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 If you have any feedback or suggestions, you can submit them by clicking Help > Feedback. You can also submit new ideas or vote for other ideas via Microsoft Feedback. Want to know more about Excel? See What's new in Excel and subscribe to our Excel Blog to get the latest updates. Stay connected with us and other Excel fans around the world - join our Excel Community and follow us on Twitter. Joe McDaid (@jjmcdaid) Program Manager, Excel 32 Likes Like 202 Comments * << Previous * + 1 + 2 + 3 + 4 + 5 * Next >> You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in. * Comment Resize Editor + height - height Co-Authors JoeMcDaid JoeMcDaid * JakeArmstrong JakeArmstrong Version history Last update: Aug 25 2022 12:22 PM Updated by: JoeMcDaid Labels Share * Share to LinkedIn * Share to Facebook * Share to Twitter * Share to Reddit * Share to Email Browse What's new * Surface Pro X * Surface Laptop 3 * Surface Pro 7 * Windows 10 Apps * Office apps Microsoft Store * Account profile * Download Center * Microsoft Store support * Returns * Order tracking * Store locations * Buy online, pick up in store * In-store events Education * Microsoft in education * Office for students * Office for schools * Deals for students and parents * Microsoft Azure in education Enterprise * Azure * AppSource * Automotive * Government * Healthcare * Manufacturing * Financial Services * Retail Developer * Microsoft Visual Studio * Window Dev Center * Developer Network * TechNet * Microsoft developer program * Channel 9 * Office Dev Center * Microsoft Garage Company * Careers * About Microsoft * Company News * Privacy at Microsoft * Investors * Diversity and inclusion * Accessibility * Security * Sitemap * Contact Microsoft * Privacy * Manage cookies * Terms of use * Trademarks * Safety and eco * About our ads * (c) Microsoft