+ Reply to Thread
Results 1 to 3 of 3

Extract String to the Left of One of Several Words

  1. #1
    Registered User
    Join Date
    10-16-2019
    Location
    California
    MS-Off Ver
    Latest
    Posts
    15

    Extract String to the Left of One of Several Words

    Hello World!

    I am trying to extract text to the left of several words. The following formula works:

    =IF(A15<>"",LEFT(A15,SEARCH("Every",A15)-1),"")

    That is, if cell A15 isn't blank, search the cell and extract text before the word "Every."

    However, if the word "Every" doesn't appear, but instead one of the following words does appear, viz., "Rental", "Moved", "Move", "One", "Weekly", or "Biweekly", I would need to extract text to the left of that instead.

    At first I wanted to just use the following equation:

    =IF(A5<>"",LEFT(A5,SEARCH(OR("Every","On","Rental","Move","Moved","One","Weekly","Biweekly"),A5)-1),"")

    but after a search on several fora I found out that the OR function doesn't work within SEARCH.

    I'm trying to figure out what to do instead. Am I on the right track with the equation below? How would I finish the equation? I want to extract the string to the left of the aforementioned words. Below is what I came up with:

    =IF(A5<>"",LEFT(A5, IF(ISERROR(SEARCH("Rental",A15,1)<>0),IF(ISERROR(SEARCH("Every",A15,1)<>0),IF(ISERROR(SEARCH("Move",A15,1)<>0),IF(ISERROR(SEARCH("Moved",A15,1)<>0),IF(ISERROR(SEARCH("One",A15,1)<>0),IF(ISERROR(SEARCH("Weekly",A15,1)<>0),IF(ISERROR(SEARCH("Biweekly",A15,1)<>0),IF(ISERROR(SEARCH("On",A15,1)<>0),...

    Thanks!

  2. #2
    Registered User
    Join Date
    10-16-2019
    Location
    California
    MS-Off Ver
    Latest
    Posts
    15

    Re: Extract String to the Left of One of Several Words

    I ended up using the below to extract left of several possible words. Is there an easier way to do this?

    =IFERROR(IF(A331<>"",LEFT(A331,SEARCH("Every",A331)-1),""),IFERROR(IF(A331<>"",LEFT(A331,SEARCH("On",A331)-1),""),IFERROR(IF(A331<>"",LEFT(A331,SEARCH("Rental",A331)-1),""),IFERROR(IF(A331<>"",LEFT(A331,SEARCH("Move",A331)-1),""),IFERROR(IF(A331<>"",LEFT(A331,SEARCH("One",A331)-1),""),IFERROR(IF(A331<>"",LEFT(A331,SEARCH("Weekly",A331)-1),""),IFERROR(IF(A331<>"",LEFT(A331,SEARCH("Biweekly",A331)-1),""),"")))))))

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract String to the Left of One of Several Words

    Try wrapping your list of words in {} to form an array constant like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    BTW: in your profile the MS-Off Ver: "latest" is a bit vague. That could be 2019 or the subscription version 365. Please update your profile to be more specific.
    Dave

+ 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] Extract Left of String
    By nicoan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-20-2018, 11:12 PM
  2. [SOLVED] Extract numbers from a the left and to the right of a string
    By T86157 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2017, 10:38 AM
  3. Replies: 4
    Last Post: 10-25-2016, 04:45 PM
  4. [SOLVED] Extract all the words from a string for processing, while leaving string intact.
    By staggers47 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-10-2014, 02:02 PM
  5. Extract n words from string
    By holmes123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-07-2012, 10:29 PM
  6. UDF to extract first three words from string
    By stodge in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-24-2011, 07:25 AM
  7. Extract last two words from string
    By dimid in forum Excel General
    Replies: 5
    Last Post: 11-28-2008, 11:07 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