+ Reply to Thread
Results 1 to 8 of 8

How to Remove Less than 4 Words in a Cell

  1. 04-07-2014, 02:14 AM #1
    jeteowo
    jeteowo is offline
    Registered User
    Join Date
    04-01-2014
    Location
    Nigeria
    MS-Off Ver
    Excel 2003
    Posts
    4

    Post How to Remove Less than 4 Words in a Cell

    I have huge list of keywords containing 1 word to 7 words in a cell..

    The keywords that having more than 3 words should remain in the cell and others need to be removed

    Here is an example of what i want

    instructions replace iphone 3gs screen
    iphone screen repair
    iphone screen repaired
    fixing cracked IPhone and Ipad screens.
    repair iPhone screen
    how to repair cracked Iphone screens
    repairing Iphones and Ipad screens
    fix cracked IPhone screens
    repair iphone
    repair ipad


    It should like this after the removal

    instructions replace iphone 3gs screen
    fixing cracked IPhone and Ipad screens.
    how to repair cracked Iphone screens
    repairing Iphones and Ipad screens
    fix cracked IPhone screens

    It is only keywords with more than 3 words that remains please, how can I do this in Excel?

    Thanks for the support.
    Reply With QuoteRegister To Reply
  2. 04-07-2014, 02:18 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: How to Remove Less than 4 Words in a Cell

    Looking for Macro or Formula solution?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  3. 04-08-2014, 09:31 PM #3
    jeteowo
    jeteowo is offline
    Registered User
    Join Date
    04-01-2014
    Location
    Nigeria
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: How to Remove Less than 4 Words in a Cell

    I need a macro solution thanks
    Reply With QuoteRegister To Reply
  4. 04-09-2014, 12:18 AM #4
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: How to Remove Less than 4 Words in a Cell

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    Please Login or Register  to view this content.
    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
    Reply With QuoteRegister To Reply
  5. 04-15-2014, 09:24 AM #5
    jeteowo
    jeteowo is offline
    Registered User
    Join Date
    04-01-2014
    Location
    Nigeria
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: How to Remove Less than 4 Words in a Cell

    Hi Sixthsense,

    Thanks for your response, i will be testing the macro and i will report back to this great forum

    thanks once again for your response

    John Eteowo
    Reply With QuoteRegister To Reply
  6. 04-19-2014, 08:21 AM #6
    jeteowo
    jeteowo is offline
    Registered User
    Join Date
    04-01-2014
    Location
    Nigeria
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: How to Remove Less than 4 Words in a Cell

    Hi Sixthsense, It is not working it is giving error below:

    It is giving

    Compile error:

    Invalid outside procedure

    thanks
    John
    Reply With QuoteRegister To Reply
  7. 04-19-2014, 08:31 AM #7
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: How to Remove Less than 4 Words in a Cell

    Hi, jeteowo,

    assuming the data is starting at A1 maybe like this (please use a copy of the worksheet for testing):
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you
    Reply With QuoteRegister To Reply
  8. 04-19-2014, 08:50 AM #8
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,516

    Re: How to Remove Less than 4 Words in a Cell

    No macro

    1) give column a header.
    2) enter
    =LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))<3
    in C2 with C1 blank
    3) Go AdvacedFilter
    select the data range to be filtered with the header
    use C1:C2 as a criteria range
    4) Delete the rows that are visible and clear the filter.
    Last edited by jindon; 04-19-2014 at 08:54 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to remove two specific words from each cell in a column
    By pahari75 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-10-2013, 06:29 PM
  2. How to Remove More or Less than 3 Words in a Cell
    By yogamast12 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-25-2012, 02:50 AM
  3. [SOLVED] Remove words
    By Grizz45 in forum Excel General
    Replies: 8
    Last Post: 04-23-2012, 05:58 AM
  4. Remove Duplicate Words from 2 Cells when 2nd cell has a sentance.
    By manoi in forum Excel General
    Replies: 10
    Last Post: 04-10-2012, 06:37 PM
  5. remove the repeated words/numbers in a cell
    By darkhorse4321 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2011, 02:17 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1