Delete A Word That Exceeds X Number Of Characters

B

brazen234

Search didn't turn up anything, so help would be appreciated.

Simple as the title sounds. I have thousands of words in column A (one
word per cell) and I would like to delete all words 20 letters or
greater.
Thanks!
 
C

CLR

=LEN(A1) in cell B1 will return the number of characters in cell
A1......copy the formula down and do Copy > PasteSpecial > Values on column
B to delete the formulas.....then sort on that column and delete all rows of
20 characters or more.....

Vaya con Dios,
Chuck, CABGx3
 
P

Peo Sjoblom

One quick and dirty solution, assume the words start in A2 with a header in
A1, in B2 put

=LEN(A2)<20

press enter, now select B2 again, then move the cursor with the mouse to the
lower right corner of B2 and when it changes to a thin cross double click
with the mouse to copy down the formula, now select A1 and B2 and do
data>filter>autofilter, filter on TRUE in the help column. select A and B
and press F5, click special and select visible cells only, then copy and
paste somewhere else, finally remove filter (data>filter and uncheck
autofilter) then delete the help column or even the original column A
--
Regards,

Peo Sjoblom

(No private emails please)
 
H

Harlan Grove

brazen234 said:
Search didn't turn up anything, so help would be appreciated.

Simple as the title sounds. I have thousands of words in column A (one
word per cell) and I would like to delete all words 20 letters or
greater.
Click to expand...

Another alternative: select the range, run the menu command Edit > Replace,
finding ????????????????????* and replacing with nothing. Then with the
range still selected, run Data > Filter > Auto Filter, selecting blanks,
then run Edit > Delete. No formulas needed.
 
Top