+ Reply to Thread
Results 1 to 2 of 2

Removing Common Words using a UDF

  1. #1
    Registered User
    Join Date
    02-09-2009
    Location
    Bosham, UK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Removing Common Words using a UDF

    I have a column of cells each containing a text string of varying lengths.

    What I want to do concatenate the text together (found a nice UDF that does this by specifying a range, rather than use the standard concatenate forumla) and then I want to remove common words such as "the", "as", "it", etc. to try and ensure the volume of characters stay beneath the character limit for a single cell for Excel 2007, which I believe is slightly over 32000.

    I could (and have so far), got a list of the top 500 words in the English language and used SUBSTITUTE to replace the common words. But this produces either a huge single cell forumla (with one SUBSTITUTE per word - but I eventually hit a limit on the length of the formula), OR, I do a SUBSTITUTE for each word I want to replace in it's own cell, creating a huge column or row of cells with a SUBSTITUTE formula.

    As I am dealing with over 13000 cells with text in them, the worksheet quickly become very unhelpful in its size.

    Is there any UDF (called, say, REMOVE_COMMON()) that could reference a cell of clutered text and then replace the common words by either looking up the words to be replaced from a separate column of words elsewhere in the workbook, or have the words to be replaced listed within the UDF itself, that one could then go in to the VBA (using Alt + F11) and add the extra words that need to be removed?

    My end goal is to create a single cell of text that I can copy off and paste the raw text into www.wordle.net. I know Wordle ignores common text, by the problem is, there is SO much text in the 13000 rows of text data, that I need to find a way to reduce the volume of words BEFORE pasting into Wordle, as Wordle itself seems to have a limit to the number of words you can paste into it.

    Hope this is clear. Any help would be much appreciated and save me a whole load of huge worksheets.

    Kind regards


    Padster

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Removing Common Words using a UDF

    As a UDF
    In A1 the text to be replaced
    in B1 : =replace_snb(A1)

    Please Login or Register  to view this content.
    As part of a macro:
    if you want to reduce the text in range("A1:Z1")

    Please Login or Register  to view this content.



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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