The second formula is more complex and makes sense if you don’t have TEXTBEFORE.
This formula is a great example of hownew functionssimplify previously complex Excel problems.
The traditional formula is more complicated than the modern approach.
Functions likeFINDandSEARCHare good at returning positions, but they cannot specify which instance of a character you want.
They always find thefirstinstance.
By default, it is set to1, meaning the first occurrence.
To find a different occurrence of a character, simply change the instance numbernand adjust thedelimiteras needed.
The formula works as follows:
This approach is effective but less intuitive than using TEXTBEFORE.
It’s important that you use a unique marker that doesn’t appear in the text string.
Customizing the traditional formula
The same formula structure can be used with a different delimiter.
When the text is not found, FIND returns a #VALUE error.
TEXTBEFORE Function
The Excel TEXTBEFORE function returns the text that occurs before a given substring or delimiter.
LEN will also count characters in numbers, but number formatting is not included.
Related videos
Excel TEXTBEFORE function
