Hi - Dave here.
One of the most interesting new functions in Excel is TEXTSPLIT. In a nutshell, TEXTSPLIT splits a text string into an array using a custom delimiter. This is a major upgrade to Excel's ability to process text, and will radically simplify many complex formulas.
As an example, I recently reworked a complicated formula that extracts the nth word from a text string. Here is the original formula:
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (C5-1)*LEN(B5)+1, LEN(B5)))
And here is same formula with TEXTSPLIT:
=INDEX(TEXTSPLIT(B5," "),C5)
I think you'll agree that the second formula is
a lot simpler :)
[
download the workbook and read the full explanation]
TEXTSPLIT is only available in Excel 365, but the original formula will work fine in older versions. The article above explains both formulas in detail. Download the workbook and have a look.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a nice weekend!
Dave
