Tjyylli

I'm using this simple formula.

=LEN(A1,20)

But I want it to be able to cut off at the last space in the fragment so there aren't any incomplete words. How can I do this?

Here is an image of the current and desired result.

I think this is what you are looking for :

English (not tested) :

=LEFT(A1,21-SEARCH(" ",SUBSTITUTE(RIGHT(A1,21)," "," ",21-LEN(SUBSTITUTE(RIGHT(A1,21)," ","")))))

French (tested) :

=GAUCHE(A1;21-CHERCHE(" ";SUBSTITUE(DROITE(A1;21);" ";" ";21-NBCAR(SUBSTITUE(DROITE(A1;21);" ";"")))))

Adapted from : https://forum.excel-pratique.com/viewtopic.php?t=3120

There is nothing, really, one can do with formulas that will check the last "word" (string of letters) is a full word. Even if you loaded a monster dictionary to spellcheck against (formula methodology would be loading it in a way that a function such as SEARCH() or VLOOKUP() can search it), there could easily be valid words not contained in it.

One could check the final "word" for things like numerals or punctuation, but then there's the whole "Even though I say "word", what qualifies that is not really a word?" problem. For instance, would an email address be a valid word? Would it only be if it were complete? How about a hashtag word or phrase? How would one even know that is complete or check its spelling since a hashtag word might be "#ijyrrbt8"?

Alright, that aside, my approach would be to first check the last character of a 21 (not 20) character string. If it were a space, or any valid punctuation for the language in question, then I'd consider anything preceding it to be complete and therefore a valid word. Do so using an IF() test on a simple OR() clause. The OR() clause would be simple, though perhaps very long, depending upon all the ways a language might continue, especially with punctuation, after a valid word. Sadly, punctuation occurs INSIDE words too as a marker, for instance, of a contraction taking place: "don't" being an example in English.

After determining that, you are either done (say a space follows, I would consider the preceding "word" to be a valid word... except as noted at the end...), or not done. If not done, one moves on to reducing the 20 character string by stripping the offending end string in it. There is a "trick" used to make FIND() work here (it needs an instance number of what you seek to look for and won't take something like "last", sadly). One uses SUBSTITUTE() to eliminate all the spaces from the cell, or string in this case, taking the LEN() of the result (subtracting it from the LEN() of the original, but you know that is 20, so... well, except you don't (see just after the example formula) so take a LEN() of the string). The result is how many of the searched for characters are present in the string. Let's say the calculation result is 4 when searching for spaces. Therefore, there are four "spaces" in the 20 character string and you can use find to locate the last one:

=FIND( " ", 4 )

Caveat: since the string COULD have a space itself as character 20, wrap the LEFT() with TRIM(): TRIM(LEFT("20 character string being examined")) to drop any last space. Good thing you took a LEN() for the TRIM()-wrapped string, eh?

OK, So you have a value now that is 1 higher than you need so subtract 1 from it and use the result as the length to specify in a LEFT() function (instead of 20):

=TRIM( LEFT( "source string", "length found above" ))

2nd caveat: Does a string ending in "Mr." satisfy? Or would one, on reflection, like such things also removed? If not, awesome, much easier. If so, and it would be a perfectly valid way to think, if one saw such as always needing the name following, and therefore it is not a valid "word" since it is missing a portion of the slightly more broadly conceived definition of "word" -- one often finds even simple things like "word" can go sideways on one. I won't even try to demonstrate anything to deal with all the weirdness that could occur here except to offer that one could, upon finding such, have another searching clause subtract 1 from the earlier searching result and so finding the next to last space rather than the last... etc.

Then apply it to a reasonably large data set and look for failures due to rare or infrequent things you did not think of being present in your source data...

Sorry to seem cynical there, but we all know the nature of a beast like this. It's definitely live and learn! Keep adding to the OR() clause and making other adjustments to help clean the data for the actual final test.