Excel has new TEXTBEFORE and TEXTAFTER functions. It doesn’t have a TEXTBETWEEN function. Let’s make one.
In the image below you can see we can combine TEXTBEFORE and TEXTAFTER to achieve a text between result.
We can use the LAMBDA function to make a TEXTBETWEEN custom function based on the formula with those two functions.
In the image below you can see the custom function in action.
Note I prefix all my custom function names with fn. This differentiates them from normal functions. Plus if you type fn in the Formula Bar you will also see a list of all the custom function in the file.
Below is the Edit Name dialog with the LAMBDA function.
I have included the LAMBDA formula below as well.
=LAMBDA(ref,txt,TEXTBEFORE(TEXTAFTER(ref,txt),txt))You can download the example Excel file at the button below.
Here is another way to write your TEXTBETWEEN function (note the double comma to make TEXTSPLIT output row-wise in order to align with the INDEX argument)…
=LAMBDA(ref,txt,INDEX(TEXTSPLIT(ref,,txt),2))
Thanks Rick – as always a more elegant solution.
This would allow selection of a nth element as well when multiple delimiters are used.
Regards
Neale
Yes, I was going to post this general function which would allow the selection of a specified “between” element…
=LAMBDA(ref,txt,cnt,INDEX(TEXTSPLIT(ref,,txt),1+cnt))
but then it would **require** the user to always specify the 1 for the first element and it would also have to be expanded to handle the error for when a user asks for an element that does not exist… I thought that would become too “messy” for the compact presentations that your blog articles tend to aim for.
Thanks again Rick – I try to keep my posts reasonably straightforward and short.
I might do a post including a selection of the element with some error handling.
Regards
Neale