SOLVED

Removing sentences with too much words

%3CLINGO-SUB%20id%3D%22lingo-sub-2865809%22%20slang%3D%22en-US%22%3ERemoving%20sentences%20with%20too%20much%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2865809%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%20I%20need%20help%20with%20my%20Excel%20file.%20I%20have%2010.000%20sentences%20and%20have%20to%20remove%20ones%20that%20have%20over%2025%20words.%20Is%20that%20somehow%20possible%3F%20Thank%20you%20for%20your%20responses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2865809%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2865956%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20sentences%20with%20too%20much%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2865956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1191059%22%20target%3D%22_blank%22%3E%40Lovro1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20A1%20contains%20the%20word%20Sentence%2C%20and%20A2%3AA10001%20contain%20the%20sentences.%3C%2FP%3E%0A%3CP%3EIn%20B1%2C%20enter%20Word%20Count.%3C%2FP%3E%0A%3CP%3EIn%20B2%2C%20enter%20the%20formula%20%3D(LEN(A2)-LEN(SUBSTITUTE(A2%2C%22%20%22%2C%22%22))%26gt%3B25%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20B10001.%3C%2FP%3E%0A%3CP%3EUse%20AutoFilter%20to%20show%20only%20the%20rows%20that%20have%20TRUE%20in%20column%20B.%3C%2FP%3E%0A%3CP%3EDelete%20all%20rows%20from%20row%202%20down.%3C%2FP%3E%0A%3CP%3EFinally%2C%20turn%20off%20the%20filter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone, I need help with my Excel file. I have 10.000 sentences and have to remove ones that have over 25 words. Is that somehow possible? Thank you for your responses.

 

 

2 Replies
best response confirmed by Lovro1 (New Contributor)
Solution

@Lovro1 

Let's say A1 contains the word Sentence, and A2:A10001 contain the sentences.

In B1, enter Word Count.

In B2, enter the formula =(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))>25

Fill down to B10001.

Use AutoFilter to show only the rows that have TRUE in column B.

Delete all rows from row 2 down.

Finally, turn off the filter.

Thank you, worked like a charm.