New text and array functions for Microsoft Excel 365 announced

2022-08-19 // By LRS Education Services
Category: Business Software Training

Microsoft has recently introduced several new text and array functions for Microsoft Excel 365, making it a lot easier to arrange, split and merge text.

The new functions are categorized as text manipulation, combining arrays, shaping arrays, and resizing arrays.  I most often have people ask how to extract or combine various pieces of data, so I’m going to introduce you to a couple of the text manipulation functions that I think will make this super easy!

If you’ve used functions in the past to manipulate text, you are likely already familiar with functions such as LEFT, RIGHT, MID, FIND, LEN, SEARCH and SUBSTITUTE (and if not, click this link to learn more.

While all of those functions allow you to manipulate text in a variety of ways, they come with some complexity and have not always been the easiest of things to learn and use. Here are three new text manipulation functions that may provide some relief:

  • TEXTBEFORE– Returns text that is before the specified delimiting characters.

Syntax =TEXTBEFORE(input_text,text_before, [n], [ignore_case])

Microsoft Excel textbefore function
TEXTBEFORE returned the characters in cell B5 that come before the comma.
  • TEXTAFTER– Returns text that is after the specified delimiting characters.

Syntax =TEXTAFTER(input_text, text_after, [n], [ignore_case])

Microsoft Excel textafter function
TEXTAFTER returned the text in cell B2 that comes after the first space.

ARGUMENTS

  • input_text–text you are searching within; wildcard characters are not allowed.
  • text_before–text that marks the point before which you want to extract.
  • text_after–text that marks the point after which you want to extract.
  • [n] – the nth instance of text_before that you want to extract.  
  • [ignore_case]– you can specify FALSE to make the search case-sensitive.

  • TEXTSPLIT– Splits text into rows or columns using delimiters.

Syntax =TEXTSPLIT(Input_Text, [col_delimiter], [row_delimiter], [Ignore_Empty]) 

ARGUMENTS

  • Input_Text–the text you want to split.
  • [col_delimiter] – one or more characters that specify where to spill the text across columns.
  • [row_delimiter] –one or more characters that specify where to spill the text down rows.
  • [Ignore_Empty]– if you specify TRUE, you will create an empty cell when two delimiters are consecutive.
Microsoft Excel textsplit function
Various examples of TEXTSPLIT.

This may look and feel familiar to you if you’ve ever used the Text-To-Columns wizard in Excel.  TextSplit() provides the same result, but as a formula instead of a wizard. And TextSplit() adds additional functionality as it can work down by rows as well as across columns.

As I mentioned, these are just a few of the many new functions. Want to see more? Check this out: New Excel Functions (microsoft.com). 

While we have great reason to be very excited about the release of these new functions, Microsoft is pulling the reigns back just slightly for some. These functions are currently available to “most” users running Microsoft 365 for Windows Beta Channel Version 2203 (Build 15104.20004) or later on Windows and on a Mac, version 16.60 (Build 22030400) or later. Notice I said “most”? Even if you find you are running the correct version/build, you may still not yet have this new availability as the roll-out by Microsoft hasn’t been pushed to all qualifying tenants yet (and there isn’t currently an advertised date for us to even know when to expect it!).

To easily determine if the new functions are in your Excel 365, in the formula bar type =text and see if the new functions TextAfter, TextBefore and TextSplit appear. If you don’t see them, keep an eye out as they may be coming soon! 

If you’d like to learn more about using functions in Excel, we’d love to have you attend an Excel instructor-led training course with us either in person or virtually using our Virtual Training platform!

If you have any questions or would like more information regarding courses scheduled at LRS Education Services, please call 877.832.0688 ext: 1493 or email us at getsmart@LRS.com.

Penny Morgan, LRS Education Services 

MCT, MCSA, MCITP, MCTS, MCSE, MCP 
Microsoft 365 Certified: Fundamentals 
Microsoft 365 Certified: Enterprise Administrator Expert 
Microsoft 365 Certified: Security Administrator Associate 
Microsoft 365 Certified: Messaging Administrator Associate 
Microsoft 365 Certified: Teams Administrator Associate 
Microsoft Certified: Azure Fundamentals 
Microsoft Certified: Azure Administrator Associate 


More Posts