intelligent split string in excel formula - full words

Question

How to split string that the words isn't split and result string is max 30 chars ?

input cell = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt'

output cell = 'Lorem ipsum dolor sit amet'

I need only formula, not VBA.

question from:https://stackoverflow.com/questions/65894264/intelligent-split-string-in-excel-formula-full-words

Answer 1

Simpler array formula:

=SUBSTITUTE(TRIM(LEFT(A1,MAX(IF(MID(A1,ROW($1:$30),1)=" ",ROW($1:$30))))),",","")

Array formula after editing is confirmed by pressing ctrl + shift + enter