Exploring 14 new Excel applications: Section 1

Microsoft not too long ago unveiled no longer one, no longer two, however 14 new Excel applications.

Sure, 14.

Now earlier than we proceed, please notice that those applications are a long way from in most cases to be had. In truth, on the time of this writing, those applications are these days to be had simplest to customers working Beta Channel, Model 2203 (Construct 15104.20004) or afterward Home windows and Model 16.60 (Construct 22030400) or afterward Mac.

In abstract, the 14 new applications are necessarily grouped as follows:

• Textual content manipulation.
• Combining arrays.
• Shaping arrays.
• Resizing arrays.

That is so much to hide — an excessive amount of for a unmarried article no less than. In an effort to digest all this knowledge in small bites, this newsletter is gifted in two portions. The primary section covers the textual content manipulation and mixing arrays teams, whilst the second one section tackles shaping and resizing arrays.  

Let’s get started with textual content manipulation

Textual content manipulation applications

Not like not unusual textual content manipulation applications akin to FIND, LEFT, LEN, MID, RIGHT, SEARCH, and SUBSTITUTE, the 3 new applications on this crew permit you to dismember textual content strings with out requiring a Ph.D. in astrophysics.

The 3 applications are:

1. TEXTBEFORE: Returns textual content that is earlier than delimiting characters.
2. TEXTAFTER: Returns textual content that is after delimiting characters.
3. TEXTSPLIT: Splits textual content into rows or columns the usage of delimiters.

Here is a peek at how they paintings.

The TEXTBEFORE serve as

The TEXTBEFORE serve as returns the string of textual content that happens earlier than a given substring (ie, a personality or set of characters) in that string. It’s the reverse of the TEXTAFTER serve as. TEXTBEFORE has the next syntax:

TEXTBEFORE(textual content, delimiter, [instance number], [ignore case])

The TEXTBEFORE serve as has the next arguments:

• textual content: That is required and represents the textual content string you’re looking inside of. Wildcard characters aren’t allowed.
• delimiter: This could also be required and represents the textual content within the textual content string that marks the purpose earlier than which you need to extract.
• example quantity: That is the primary non-compulsory argument and denotes the nth example of the delimiter earlier than which you need to extract. Through default, this is the same as one [1]. If a adverse quantity is used right here, the serve as begins looking for the delimiter from the top somewhat than the start.
• forget about case: This, too, is an non-compulsory argument and determines whether or not the hunt is case-sensitive or no longer. The default is FALSE, because of this the seek for the delimiter is case-sensitive; explicitly use TRUE to make the hunt case insensitive.

It must be additional famous that:

• Excel must go back an #N/A error if the delimiter is an empty string, however the present Beta model seems to go back a clean.
• Excel returns a #VALUE! error if the example quantity is 0 (the default is one).
• Excel returns an #N/A error if the delimiter does no longer happen inside the textual content.
• Excel returns an #N/A error if the example quantity is larger than the choice of occurrences of the delimiter inside the textual content.

Please see the examples under: