+ Reply to Thread
Results 1 to 2 of 2

UDF to extract first three words from string

  1. #1
    Registered User
    Join Date
    01-28-2010
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Unhappy UDF to extract first three words from string

    I am currently using a number of formulas to extract the first three words from a string.

    A1 - contains a sentence of variable length eg "to supply and install suspended ceiling aircon system to the first floor"

    B1 - counts the number of words by using =IF(LEN(TRIM(cleanwords(A1)))=0,0, LEN(TRIM(cleanwords(A1)))-LEN(SUBSTITUTE(TRIM(cleanwords(A1))," ",""))+1)

    [cleanwords is a UDF to remove a list of 100+ words I am not interested in eg "up down in of to from supply and install" etc]

    C1 - calculates the max number of words to extract by using =IF(A1>=3,3,IF(A1>1,2,IF(A1=1,1,0)))

    So in my example the max number of words wanted is 3

    D1 =IF(ISBLANK(A1),"",LEFT((((A1))),FIND("^",SUBSTITUTE((((A1)))&" "," ","^",C1))-1))

    This would return "suspended ceiling aircon"

    E1 uses to the result in D1 to vlookup data from a master product category

    I am unable to combine the three elements of the formula into a single formula or UDF and would be grateful for any help!

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,444

    Re: UDF to extract first three words from string

    Hi,

    There's some code that could be modified to suit at Ozgrid....

    http://www.ozgrid.com/VBA/extract-words-function.htm

    and some more here:

    http://www.meadinkent.co.uk/xlextracttext.htm
    Last edited by sweep; 01-24-2011 at 07:30 AM.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

+ 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