+ Reply to Thread
Results 1 to 6 of 6

Search multiple phrases in a cell and return the first phrase found

  1. #1
    Registered User
    Join Date
    12-24-2017
    Location
    houston
    MS-Off Ver
    2010
    Posts
    6

    Search multiple phrases in a cell and return the first phrase found

    Hello Gurus! I’ve been spending too long trying to figure this out 🤯

    Here is how my data set looks: 👀

    Exception message text (Column X) | Expected output
    12/31 valid to cancel *psm 10/31 valid to reschedule in | Valid to Cancel
    12/31 valid to reschedule in *psm 10/31 valid to reschedule out | Valid to Reschedule in
    12/31 valid to *psm 10/31 valid to reschedule in 11/01 unable to cancel | Valid to Reschedule in
    12/31 valid to Reschedule out *psm 10/31 valid to reschedule in | Valid to Reschedule out

    I’ve used the search function with a array constant and many other functions like index match etc. I’m just not that savvy in combining them to get the first phrase in a string with potential 1,2,3 phrases optional

    Let me know if this still does not make sense!!!

    My phrases I’m interested in are:
    Valid to cancel
    Valid to Reschedule out
    Valid to Reschedule In

    I want the first phrase that appears in the cell which has unpredictable character types and lengths

  2. #2
    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: Search multiple phrases in a cell and return the first phrase found

    Hi pmeltzers. Welcome to the forum.

    Try this.

    With a lookup table (column D here) array enter this formula in B2 and fill down.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    D
    1
    Input
    Output
    Valid to cancel
    2
    12/31 valid to cancel *psm 10/31 valid to reschedule in | Valid to Cancel
    Valid to cancel
    Valid to Reschedule out
    3
    12/31 valid to reschedule in *psm 10/31 valid to reschedule out | Valid to Reschedule in
    Valid to Reschedule In
    Valid to Reschedule In
    4
    12/31 valid to *psm 10/31 valid to reschedule in 11/01 unable to cancel | Valid to Reschedule in
    Valid to Reschedule In
    5
    12/31 valid to Reschedule out *psm 10/31 valid to reschedule in | Valid to Reschedule out
    Valid to Reschedule out
    Dave

  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: Search multiple phrases in a cell and return the first phrase found

    Anther way. This one does not have to be array entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    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: Search multiple phrases in a cell and return the first phrase found

    Another one that does not have to be array entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-24-2017
    Location
    houston
    MS-Off Ver
    2010
    Posts
    6

    Re: Search multiple phrases in a cell and return the first phrase found

    This is exactly what I needed, never heard of the aggregate function, and so happy that i'm aware of it now!!! thank you very much. I ended up using the following formula:


    =IFERROR(LOOKUP(2,1/(AGGREGATE(15,6,SEARCH({"Valid to Cancel";"Valid to Reschedule Out";"Valid to Reschedule In"},X2),1)=SEARCH({"Valid to Cancel";"Valid to Reschedule Out";"Valid to Reschedule In"},X2)),{"Valid to Cancel";"Valid to Reschedule Out";"Valid to Reschedule In"}),"")


    Using this as an array allowed me to grab the first phrase in the string that appeared. I think I'll use the table array as well for another project! @FlameRetired your help is invaluable.

  6. #6
    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: Search multiple phrases in a cell and return the first phrase found

    Glad to help. Yes AGGREGATE is a very helpful function. I'm pleased it got added Excel 2010 and that you find it useful.

    Thank you for the feedback, added rep and marking your thread Solved. That helps.
    Last edited by FlameRetired; 12-26-2017 at 04:11 PM.

+ 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. Search and If found return adjacent cell [Solved]
    By jpullen88 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-29-2016, 03:49 PM
  2. Replies: 2
    Last Post: 10-09-2014, 05:14 AM
  3. Replies: 3
    Last Post: 01-21-2014, 05:15 PM
  4. Search cell for multiple text , return comma separated text in separate cell if found
    By dangerdoug in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2013, 01:52 PM
  5. [SOLVED] Search for a string within a cell, if found, return value of 1
    By brookenovak in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-07-2013, 07:39 PM
  6. How do I search multiple separate phrases at once?
    By Dcrader in forum Excel General
    Replies: 4
    Last Post: 01-02-2009, 02:09 PM
  7. [SOLVED] Search a Range for a phrase and Format cell if found
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2005, 06:05 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