Hi - Dave here.
Happy Friday!
I was updating some formulas last week on the Exceljet site and ran into another great example of how new functions like TEXTAFTER can
really simplify complicated formulas.
In the worksheet below, the "old" formula to get the last name from a name looked like this:
=MID(B5,FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))+1,100)
The "new" formula looks like this:
=TEXTAFTER(B5," ",-1)
I don't know about you, but I prefer the second option :)
[
Download the workbook and read the full explanation]
The cool thing in the example is the
negative instance number inside TEXTAFTER. Positive instance numbers count from the right, while negative numbers count from the left. So, the -1 in this case tells TEXTAFTER to get all text after the
last space. This is an excellent feature, useful in many situations.
Alas, the TEXTAFTER function is only available in Excel 365 for now. In older versions of Excel, you can use the more complex option above. The link above includes this formula with a full explanation.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
