4 New’ish Text Functions in Microsoft Excel That Every Engineer Should Know

Let’s be honest, Microsoft Excel is the most commonly used engineering software tool out there. And that is coming from a person who has used Ansys for 35+ years. It is something everyone has, and everyone can use without much effort – except for one thing. It has always been a pain to split and join text. Until now. Microsoft (finally) added some functions that have existed in almost every programming language out there for decades.

Note: These are only available in Microsoft 365, not older versions of the program. You can see all the functions that have been added to Excel in Microsoft 365 here.

I’ve found these very useful when taking data from tests or simulations and massaging it. Even though it took a while to fix this, I’m thankful that the Microsoft developers did add this capability. They have been around since 2021, but I only stumbled across them by accident last year. When I brought them up with other people, they didn’t know that these useful functions were available. So, why not share them with the interweb and make text parsing in Excel easier for more people?

Doing away with Left()/Right()/Mid() and Find()

If I ever run into someone on the Excel dev team the only question I will ask them is why they made us suffer with nested functions to parse a string for so long. This is what grabbing the first string before a period looked like before the new function:

D5: Jump. Then, Run. After that - stop, drop, and roll. Be Safe

Function: =LEFT($D$5,FIND(".",$D$5)-1)

Result: Jump

You had to use FIND() to return the number of characters counting from the left to the first “.”. Then subtract one to use LEFT() to give you the number of characters to extract.

If you wanted to pull from the end of a string, you had to introduce the LEN() function because FIND() returns the count from the left and right uses the count from the right.

So:

Function: =RIGHT($D$5,LEN($D$5)- FIND(".",$D$5))

Result: Then, Run. After that - stop, drop, and roll. Be Safe

And if you want to get fancy and get the string after the second “.” And before the “-“ you would need to do this:

Function: =LEFT(RIGHT($D$5,LEN($D$5)-FIND(".",$D$5,FIND(".",$D$5)+1)),FIND("-",RIGHT($D$5,LEN($D$5)-FIND(".",$D$5,FIND(".",$D$5)+1)))-1)

Result: After That.

The new functions are much, much better:

=TEXTBEFORE(TEXTAFTER(D5,".",2),"-")

But I’m getting ahead of myself. Let’s look at each one using our example sentence.

TEXTBEFORE(): Get Text Before a Delimiter

As the name implies, TEXTBEFORE() returns the string before a delimiter that you specify. And it has some other options.

=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

text:	The string to work with
delimiter:	Text, in quotes, that you want to use as a delimiter.
instance_num:	Which instance of the delimiter to use. Two cool tricks are you can put in an array, or if you want to count from the end of the sting, use a negative number. The default is the first delimiter in the string.
match_mode:	Set this to 1 to make your search for the delimiter case sensitive.
match_end:	This is a bit tricky. If set to 1, it treats the end of the string as a delimiter. So, if the delimiter is not strong, it returns the whole string instead of an error.
If_not_found:	You can also avoid getting an #N/A when the delimiter is not found by specifying your own return value or call a different function.

Only the first two are required arguments, anything in brackets is optional.

Here is the string again, in D5: Jump. Then, Run. After that – stop, drop, and roll. Be Safe

To get the first substring before the period, “Jump,” we use:

String: Jump. Then, Run. After that - stop, drop, and roll. Be Safe

Function: =TEXTBEFORE($D$5,".")

Return: Jump

Next, let’s get everything before the second period. We put 2 in for instance_num:

String: Jump. Then, Run. After that - stop, drop, and roll. Be Safe

Function: = =TEXTBEFORE($D$5,".",2)

Return: Jump. Then, Run

To switch things up, let’s grab the text before the 2^nd comma from the end with a -2 for the instance_num:

String: Jump. Then, Run. After that - stop, drop, and roll. Be Safe

Functions: =TEXTBEFORE($D$5,",",-2)

Return: Jump. Then, Run. After that – stop

The table below shows these examples and how to put in multiple delimiters and instance_nums. You can get very fancy, especially with the if_not_found argument. The last example in the table is the equivalent LEFT()/FIND() way of doing it.

Another thing to note is that if the function returns more than one argument, like the “1st & 2nd” example below, Excel fills cells on the same row and to the right.

TEXTBEFORE():
String in D5:	Jump. Then, Run. After that – stop, drop, and roll. Be Safe
1st String	=TEXTBEFORE($D$5,”.”)	Jump
2nd String	=TEXTBEFORE($D$5,”.”,2)	Jump. Then, Run
2nd from end	=TEXTBEFORE($D$5,”.”,-2)	Jump. Then, Run
1st & 2nd	=TEXTBEFORE($D$5,”.”,{1,2})	Jump | Jump. Then, Run
Multiple	=TEXTBEFORE($D$5,{“.”,”,”,”-“},{1,2,4,-2})	Jump | Jump. Then | Jump. Then, Run. After that | Jump. Then, Run. After that – stop, drop
Can’t find Error	=TEXTBEFORE($D$5,”x”,2)	#N/A
Can’t find Text	=TEXTBEFORE($D$5,”x”,2,,,”OOPS!”)	OOPS!
End is a Delim	=TEXTBEFORE($D$5,”x”,,,1)	Jump. Then, Run. After that – stop, drop, and roll. Be Safe
Left 1st String	=LEFT($D$5,FIND(“.”,$D$5)-1)	Jump

You can find the official documentation here.

TEXTAFTER(): Get Text After a Delimiter

Again, the name is the bulk of the documentation. It returns the text after a delimiter. The format is and arguments are the same.

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

text:	The string to work with
delimiter:	Text, in quotes, that you want to use as a delimiter.
instance_num:	Which instance of the delimiter to use. Two cool tricks are you can put in an array, or if you want to count from the end of the sting, use a negative number. The default is the first delimiter in the string.
match_mode:	Set this to 1 to make your search for the delimiter case sensitive.
match_end:	This is a bit tricky. If set to 1, it treats the end of the string as a delimiter. So, if the delimiter is not strong, it returns the whole string instead of an error.
If_not_found:	You can also avoid getting an #N/A when the delimiter is not found by specifying your own return value or call a different function.

To see the difference, let’s go with the basic command for both TEXTBEFORE() and TEXTAFTER:

String: Jump. Then, Run. After that – stop, drop, and roll. Be Safe

Function: =TEXTBEFORE($D$5,".")

Return: Jump

Function: =TEXTAFTER($D$5,".")

Return: Then, Run. After that - stop, drop, and roll. Be Safe

Here are some examples to show how it works. The RIGHT/LEN/FIND example for second delimiter from the left is included for comparison.

=TEXTAFTER($D$5,”.”)	Then, Run. After that – stop, drop, and roll. Be Safe
=TEXTAFTER($D$5,”.”,2)	After that – stop, drop, and roll. Be Safe
=TEXTAFTER($D$5,”.”,-2)	After that – stop, drop, and roll. Be Safe
=TEXTAFTER($D$5,”.”,{1,2})	Then, Run. After that – stop, drop, and roll. Be Safe | After that – stop, drop, and roll. Be Safe
=TEXTAFTER($D$5,{“.”,”,”,”-“},{1,2,4,-2})	Then, Run. After that – stop, drop, and roll. Be Safe | Run. After that – stop, drop, and roll. Be Safe | stop, drop, and roll. Be Safe | and roll. Be Safe
=TEXTAFTER($D$5,”x”,2)	#N/A
=TEXTAFTER($D$5,”x”,2,,,”OOPS!”)	OOPS!
=TEXTAFTER($D$5,”x”,,,1)
=RIGHT($D$5,LEN($D$5)- FIND(“.”,$D$5))	Then, Run. After that – stop, drop, and roll. Be Safe
=RIGHT($D$5,LEN($D$5)-FIND(“.”,$D$5,FIND(“.”,$D$5)+1))	After that – stop, drop, and roll. Be Safe

You can find the full official documentation here.

TEXTSPLIT(): Break a String up With One or More Delimiters

The next function, TEXTSPLIT() is basically a formula for the menu command Data > Text to Columns. It is a bit more sophisticated than the command because you can break things up by column to.

= TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

Text	The text you want to split. Required.
col_delimiter	The text that marks the point where to spill the text across columns.
row_delimiter	The text that marks the point where to spill the text down rows. Optional.
ignore_empty	Specify TRUE to ignore consecutive delimiters. Defaults to FALSE, which creates an empty cell. Optional.
match_mode	Specify 1 to perform a case-insensitive match. Defaults to 0, which does a case-sensitive match. Optional.
pad_with	The value with which to pad the result. The default is #N/A.

Like that function, it uses a “spill” where the results are generated across rows and columns. Sometimes it is useful to have a function rather than use the command. Also, it is more robust when you have multiple columns or data to split. Another capability that we won’t go into much is you can split a cell into multiple rows as well.

Here is what it looks lie for a simple string “red, green, and yellow” in D33:

=TEXTSPLIT(D33,” “)

red,

green,

and

yellow

And then if we break up rows with “and”

=TEXTSPLIT($D$33,”,”,” and”,TRUE,,””)	red	green
	yellow

This one is a bit tricky beyond the basic function. So check out the official documentation here to learn more.

TEXTJOIN(): Combine Values in Cells Into a Delimited String

I’ve not had a lot of uses for this function, but when I did it was super helpful. There is a concatenate function and you can also use “&” to join strings. This sort of does it for you and inserts your preferred delimiter.

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Delimiter	A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text.
ignore_empty	If TRUE, ignores empty cells.
text1	Text item to be joined. A text string, or array of strings, such as a range of cells.
[text2, …]	Additional text items to be joined. There can be a maximum of 252 text arguments for the text items, including text1. Each can be a text string, or array of strings, such as a range of cells.

Here is an example where I’ve used my favorite non-standard delimiter “|” to separate some strings:

Data:	Apples, Red and Green	Oranges	Grapes
=TEXTJOIN(“|”,,D43:F43)	Apples, Red and Green|Oranges|Grapes

It’s not something you will need a lot, but handy when you do. The full help is here.

Give These New Excel Text Functions a Try

This has been a very high-level overview of the capability. Nothing is too complex, but you can get very sophisticated if you want. And, as with most Excel functions, the best way to learn how it really works is to use it on your real data.

Here is a spreadsheet I used to play around with the functions, in a Zip file so you can download it easily.

Here at PADT, we are all about the right solution for the right problem, and often, that right solution is an Excel spreadsheet. If you have any engineering needs, maybe a custom spreadsheet to do some quick calculations, reach out to us and have a chat with our engineers.

Python in Excel?

And wait, there is more.

While writing this post, I discovered that as of August of 2023, you can run Python formulas in Excel! After I find some time to play with that, I’ll add a blog post on what I find. More to come…