Truncating a cell entry to the last full word

[Pat]

Hi All,

I've searched in this amazing forum and found a forumla to truncate a
cell length: (assuming data is in A2)
=IF(LEN(A2)>399,LEFT(A2,399),A2 & REPT(" ",399-LEN(A2)))

I'm trimming down text entries in a large spreadsheet where the
maximum number of characters in a cell has to be under 400.

Just wondering if anybody has any handy tips on truncating the cell as
above, but also cutting it off at the last whole word.

One thought I had was a formula that does the following: (assuming
text data of 399 characters is in A1)
"If last character in A1 is not a blank (space), then delete last
character in A1"

That would eliminate the last character of cell A1 that doesn't end in
a blank (space).

Thereafter it would just be a case of reapplying the formula a few
times until all the characters of a truncated word have been deleted
and the cell ends with a blank (space) and that would mean that the
cell is under 399 characters in length and ends with a blank (space) =
truncation to the last full word.

Does that sound feasible? No need for a macro that repeats the formula
until the cell ends with a blank (space), I can repeat the forumla
manually to have the desired effect.

I would greatly apprecaite any help on this at all,

Thanks in advance,

All the best,

Pat

 

[Gary''s Student]

Do you want the function to return the last word in a sentence or the
sentence with the last word removed?

 

[Pete_UK]

Hi Pat,

this formula will find the position of the last space up to 399
characters from a string in A2:

=FIND(CHAR(1),SUBSTITUTE(LEFT(A2,399)," ",CHAR(1),(399-
LEN(SUBSTITUTE(LEFT(A2,399)," ","")))))

so, if you have such a long string, then this will return only
complete words up to but excluding that last space:

=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(LEFT(A2,399)," ",CHAR(1),(399-
LEN(SUBSTITUTE(LEFT(A2,399)," ","")))))-1)

This more-complete formula will return either the complete text if it
is less than 399 characters, or text truncated to the last complete
word so that it is less than 400 characters:

=IF(LEN(A2)>399,LEFT(A2,FIND(CHAR(1),SUBSTITUTE(LEFT(A2,399),"
",CHAR(1),(399-LEN(SUBSTITUTE(LEFT(A2,399)," ","")))))-1),A2)

You could then apply your formula in to add trailing spaces to the end
of this result in a separate cell, or you could combine that, as
follows:

=LEFT(IF(LEN(A2)>399,LEFT(A2,FIND(CHAR(1),SUBSTITUTE(LEFT(A2,399),"
",CHAR(1),(399-LEN(SUBSTITUTE(LEFT(A2,399)," ","")))))-1),A2)&REPT(" ",
399),399)

i.e. add on loads of spaces then just take the first 399 characters.

Hope this helps.

Pete

 

[Pat]

Hi Pete,

That's fabulous stuff. How many maths A-Levels did you do? ;-)

Thanks a million,

Cheers,

Pat

 

[Pete_UK]

You're welcome, Pat. Glad it worked for you.

I did my A levels when they were proper qualifications, so they are
probably worth several times today's offerings !! <bg>

Pete