+ Reply to Thread
Results 1 to 8 of 8

How to filter words by number of letters.

  1. #1
    Registered User
    Join Date
    03-26-2022
    Location
    Ceuta
    MS-Off Ver
    Profesional Plus 2019
    Posts
    2

    Talking 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.


    Thank you in advance
    Last edited by Miguel Martínez; 03-27-2022 at 02:31 PM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,832

    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.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,896

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,832

    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. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    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. #6
    Registered User
    Join Date
    03-26-2022
    Location
    Ceuta
    MS-Off Ver
    Profesional Plus 2019
    Posts
    2

    Re: How to filter words by number of letters.

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

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: How to filter words by number of letters.

    You're welcome - thanks for the rep.

    Pete

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,896

    Re: How to filter words by number of letters.

    Quote Originally Posted by 6StringJazzer View Post
    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Numerical Value of letters in words
    By jomili in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 01-19-2022, 09:22 PM
  2. Replies: 7
    Last Post: 11-26-2021, 06:04 AM
  3. [SOLVED] Count the number of first letters derived from words in a row
    By Modify_inc in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-08-2019, 01:53 PM
  4. Removing words under 4 letters
    By jeremyh09 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-30-2013, 12:23 AM
  5. COMBINE LETTERS into WORDS
    By JPSQ in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-10-2012, 06:28 AM
  6. Replies: 7
    Last Post: 01-24-2012, 03:36 PM
  7. [SOLVED] How do you sort words in Excel by the number of letters in a word
    By Kinger in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-02-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1