P
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
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