Working from the inside out, SUBSTITUTE is configured to replace the nth occurence of a space character, where n comes from column C, the text comes from column B, and “#” is hardcoded. The resulting string is returned to the FIND function, configured to look for “#”. Since the “#” is the 12th character in the text, FIND returns 12. We don’t want to include the space character itself in, so we subtract 1: LEFT returns the final result from the formula, “The cat sat”. Note: the pound character ("#") is arbitrary and can be replaced with any character that won’t appear in the text.
Add elipses or other character
To add “…” to the end of the trimmed text, use concatenation like this: You can replace “…” with anything you like.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
