Explanation
At the core, this formula uses an array operation to generate an array of letters from the input text, translates each letter individually to a number, then joins all numbers together again and returns the output as a string.
To parse the input string into an array or letters, we use MID, ROW, LEN and INDIRECT functions like this:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN returns the length of the input text, which is concatenated to “1:” and handed off to INDIRECT as text. INDIRECT evaluates the text as a row reference, and the ROW function returns an array of numbers to MID:
MID(B5,{1;2;3},1)
MID then extracts one character for at each starting position and we have:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,{"a";"b";"c"})),xtable,2,0))
Essentially, we are asking VLOOKUP to find a match for “a”, “b”, and “c” at the same time. For obscure reasons , we need to “dereference” this array in a particular way using both the T and IF functions. After VLOOKUP runs, we have:
=TEXTJOIN("",1,{9;4;6})
and TEXTJOIN returns the string “946”.
Output a number
To output a number as final result (instead of a string), add zero. The math operation will coerce the string into a number.
Sum numbers
To sum the numbers together instead of listing them, you can replace TEXTJOIN with SUM like this:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Note: the TEXTJOIN function was introduced via the Office 365 subscription program in 2018.
Explanation
We need a way to split text at a certain marker that corresponds to a certain number of words. Excel doesn’t have a built-in function to parse text by word, so are using the SUBSTITUTE function’s “instance” argument to replace an “nth space” character with the pound sign (#), then using FIND and LEFT to discard all text after the marker.
Working from the inside out, SUBSTITUTE is configured to replace the nth occurrence of a space character, where n comes from column C, the text comes from column B, and “#” is hard coded.
=SUBSTITUTE(B5," ","#",C5)
=SUBSTITUTE("The cat sat on the mat."," ","#",3)
="The cat sat#on the mat."
The resulting string is returned to the FIND function, configured to look for “#”.
=FIND("#","The cat sat#on the mat.)
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(B5,12-1)
=LEFT(B5,11)
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 ellipses or another character
To add “…” to the end of the trimmed text, use concatenation like this:
=LEFT(B5,FIND("#",SUBSTITUTE(B5," ","#",C5))-1)&"..."
You can replace “…” with anything you like.
