Delete A Word That Exceeds X Number Of Characters

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!

=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



"brazen234" <[email protected]> wrote
in message news:[email protected]...
>
> 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!
>
>
> --
> brazen234
> ------------------------------------------------------------------------
> brazen234's Profile:
http://www.excelforum.com/member.php...fo&userid=9952
> View this thread: http://www.excelforum.com/showthread...hreadid=497103
>

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)


"brazen234" <[email protected]> wrote
in message news:[email protected]...
>
> 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!
>
>
> --
> brazen234
> ------------------------------------------------------------------------
> brazen234's Profile:
> http://www.excelforum.com/member.php...fo&userid=9952
> View this thread: http://www.excelforum.com/showthread...hreadid=497103
>

"brazen234" <[email protected]>
wrote...
>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.

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.