+ Reply to Thread
Results 1 to 2 of 2

Find specific keywords in a string and return them in respective column.

  1. #1
    Registered User
    Join Date
    01-15-2010
    Location
    India
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    1

    Find specific keywords in a string and return them in respective column.

    Hi All,

    I have a following requirement.
    I want to search for three words in a string, if any of the keywords is found first in the string, then that keyword should be placed in the cell left of the cell in which string is there.

    For example:
    Cell B1 contains "System PR1 is connected to PC1 with an interface at PB1."
    No I want to search for any of the following keywords:
    PR1 or PC1 or PB1.
    Since it will find PR1 first in the string, so in cell A1, it should return PR1, and should omit searching remaining keywords, since it has found the first keyword that I am looking for.

    Let us suppose if PR1 is not in the string then it should return PC1 in the cell A1 since it is a second keyword I am looking for in the string if available, if PC1 is also not in the string, then it should search for PB1 in the string and if it finds PB1 in the string then it should return PB1 in the cell A1.

    If none of the keywords are part of the string, then it should return whatever I want to return like "Null" or "Not Found".

    I tried the Find command but it is not working in the above scenario, and does not start finding for the second keyword if the first keyword is not found.

    Any help would be of great help for omitting the manual work I am performing in Excel.


    Thank You,
    Saurabh.

  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,440

    Re: Find specific keywords in a string and return them in respective column.

    Hi,

    =IF(ISNUMBER(FIND("PR1",B1)),"PR1",IF(ISNUMBER(FIND("PC1",B1)),"PC1",IF(ISNUMBER(FIND("PB1",B1)),"PB1","Null")))

    Gives the response you require.
    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)

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