# How to filter words by number of letters.

1. ## How to filter words by number of letters.

Hi everyone, this is my first post in this forum and I am very pleased to know that this community exists, I found it very helpful & useful

I was wondering if there was any way in Excel to filter in a colum by the number of characters, specifically 3 letters or less.

WhatsApp Image 2022-03-26 at 7.33.37 PM.jpeg

In the column of the photo you can see many names written. I just want to filter those words that contains 3 or less characters and eliminate them without eliminating the other words. That means for example, in G1, I want to transform "A Clarinet I" into "Clarinet" by just deleting the "A" and the "I". I would need to do it in the whole column by once.

2. ## Re: How to filter words by number of letters.

Welcome to the Forum Miguel Martínez!

When asking a question, it is much better (and easier) to just attach your Excel file. It looks like you took a photo of your screen and attached the photo. See yellow banner at the top of the page.

I don't think there is a simple way to do this but it's possible. We will need your file to test solutions, however.

3. ## Re: How to filter words by number of letters.

Agree with Jeff. However, if I understand you correctly, you could try using a helper column with the formula =LEN(G1) copied down. Then, apply filters (Home/Editing/Sort & Filter), then sort based on that column for anything less 4

4. ## Re: How to filter words by number of letters.

Ford, he wants to edit each cell to remove any words that are fewer than 4 characters. Note remark at the end:
I want to transform "A Clarinet I" into "Clarinet" by just deleting the "A" and the "I".
So the overall length of the cell does not come into play. Using 2019 it would be difficult with formulas (I don't know how you would do it but there is probably a way), and I could do it with VBA.

5. ## Re: How to filter words by number of letters.

Here's a manual way of doing it:

Use a formula like this (e.g. in H1):

=" "&G1&" "

Copy this down to the bottom of your list, and then fix the values (i.e. select them all in that column, click on Copy | Paste Special | Values | OK then press the Esc key). Then with the range of cells selected, you can use Find & replace (CTRL-H) as follows:

Find What: space?space
Replace with: space
Click Replace all

That will get rid of most of the single characters, but if there are more than one within a cell you may need to click on Replace All with the same cells selected a few times.

Then instead of a single ? you should change this to a double ? in the Find What box, i.e. space??space. then click Replace All again (and again if necessary).

Repeat this once more, but with 3 question marks, i.e. space???space, in the Find What box, to get rid of 3-letter words.

You may be left with a few spurious spaces, but you can get rid of these by applying this formula (e.g. in I1):

=TRIM(H1)

Hope this helps.

Pete

6. ## Re: How to filter words by number of letters.

Thank you Pete, you solved the problem. It works, thank you a lot genius

7. ## Re: How to filter words by number of letters.

You're welcome - thanks for the rep.

Pete

8. ## Re: How to filter words by number of letters.

Originally Posted by 6StringJazzer
Ford, he wants to edit each cell to remove any words that are fewer than 4 characters. Note remark at the end:

So the overall length of the cell does not come into play. Using 2019 it would be difficult with formulas (I don't know how you would do it but there is probably a way), and I could do it with VBA.
Guess it helps if you read all the way to the bottom huh?

There are currently 1 users browsing this thread. (0 members and 1 guests)