+ Reply to Thread
Results 1 to 8 of 8

if formula to extract specific words from a dynamic range

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,040

    if formula to extract specific words from a dynamic range

    I have data in column A and have to extract specific words which vary by rows TO COLUMN b

    e.g SADC, EC1, EC2, EUR, EUR1,IOC, EC3, COMESA 1 , COMESA

    Actually using formula for only one reference


    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,018

    Re: if formula to extract specific words from a dynamic range

    Is it always going to be the third word?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,040

    Re: if formula to extract specific words from a dynamic range

    No , it varies between 3 to 6

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: if formula to extract specific words from a dynamic range

    Use lookup table for searchable values.
    Enter formula in B2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C
    1 Articles Description Preferential Code Lookup Table
    2 22041090 WINES SADC
    3 22082090 BRANDY SADC (U117020) SADC EUR1
    4 22082090 BRANDY SADC (U117020) SADC EC3
    5 22083090 WHISKY (43%) COMESA
    6 22084090 CANE CAPE SADC (U117020) SADC IOC
    7 22084090 CANE CAPE SADC (U117020) SADC EC
    8 22085021 GINS EUR1 (U117020) EUR1 EC2
    9 22085021 GINS SADC (U117021) SADC EC1
    10 22085021 GINS SADC (U117049) SADC COMESA 1
    11 22085021 GINS COMESA (U117049) COMESA COMESA 2
    12 22085021 GINS EC3 EC EC 1
    13 22086010 VODKA COMESA 1 U117020) COMESA 1
    14 22086010 VODKA SADC (U117020) SADC
    15 22086010 VODKA SADC (U117020) SADC
    16 22086010 VODKA COMESA 2 (U117020) COMESA 2
    17 22086010 VODKA IOC (U117021) IOC
    18 22086010 VODKA EC (U117049) EC
    19 22086010 VODKA SADC (U117049) SADC
    20 22086010 VODKA
    21 22087019 LIQUEUR EC 1 (U117021) EC 1
    22 22087019 LIQUEUR SADC (U117021) SADC
    23 22087019 LIQUEUR EC2 (U117021) EC2
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,797

    Re: if formula to extract specific words from a dynamic range

    Is that an error in A13 and should there be a "("

    i.e should it be
    22086010 VODKA COMESA 1 (U117020)
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,040

    Re: if formula to extract specific words from a dynamic range

    Right it should as below, typo error,somtimes it happens

    22086010 VODKA COMESA 1 (U117020)

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: if formula to extract specific words from a dynamic range

    Here is another way. It should give you more accurate results
    Must still use look up table.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AlKey; 02-16-2018 at 01:18 PM.

  8. #8
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,040

    Re: if formula to extract specific words from a dynamic range

    Thank you Alkey for solutions provided , Worked as requested

+ 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. [SOLVED] formula to extract specific word from a dynamic range
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2018, 10:51 AM
  2. [SOLVED] vba to extract rows with specific words on another sheet
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2017, 10:32 AM
  3. [SOLVED] Formula to extract the text before one of two specific words
    By The_Snook in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-02-2017, 07:21 AM
  4. [SOLVED] Extract specific words from large text
    By jesse030 in forum Excel General
    Replies: 7
    Last Post: 04-15-2016, 07:27 AM
  5. [SOLVED] looking formula for Extract Specific WORDs in existing LONG Words
    By santosh226001 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-07-2013, 08:21 AM
  6. How can i extract specific words from a cell?
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 06-17-2013, 12:27 PM
  7. Extract specific words from cells
    By Santa-D in forum Excel General
    Replies: 6
    Last Post: 06-18-2006, 11:10 PM

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