show first 50 characters in cell with only complete words

[james19]

Hi everyone,

I'm trying to write formula (in Excel or using VBA) which shows the first 50 characters (or less) from a cell without cutting short any words. The best way I can explain this is with the example below:

"Jack and Jill went up the hill To fetch a pail of water." - 56 characters but my maximum is 50

"Jack and Jill went up the hill To fetch a pail of" - " water." has been removed to make the length less than 50 characters but still make sure only full words have been entered.

Any help would be greatly appreciated.

Thanks

[sweep]

Hi,

Try this:

=LEFT(A1,IF(AND(LEN(A1)>50,ISNUMBER(FIND(" ",A1))),FIND("#",SUBSTITUTE(LEFT(A1,50)," ","#",50-LEN(SUBSTITUTE(LEFT(A1,50)," ","")))),50))

[oeldere]

See the green cells in the attached file.

Please reply.

[samba_ravi]

if your data is in A1 TRY THIS
=IFERROR(LEFT(A1,IF(LEN(A1)<=50,A1,51-MATCH(" ",INDEX(MID(LEFT(A1,51),51-ROW($1:$51)+1,1),0),0))),A1)

[james19]

These are great, thank you! I've just been going through each example to see if I can work them out. I went for nflsales' solution just because it was the easiest to understand. Sweep I found that your formula can leave a blank space at the end but it's still really appreciated. Thanks guys