+ Reply to Thread
Results 1 to 12 of 12

Search for keyword in cell and return the numbers following the keyword in adjacent cell

  1. #1
    Registered User
    Join Date
    03-12-2009
    Location
    Coyanosa, USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Search for keyword in cell and return the numbers following the keyword in adjacent cell

    Hello,

    I am working on an Excel 2010 worksheet organized as as follows:

    A B
    1 blah..blah..KEYWORD 11.1..blah..blah..
    2 blah..blah..KEYWORD 0.1..blah..blah..
    3 blah..blah..KEYWORD 100..blah..blah..
    .
    blah..blah = both text and numeric. There's a space between KEYWORD and number

    I would like to be able to have the numbers (integers, or having 1 or 2 decimal places) following the KEYWORD returned in blank adjacent cells as shown below:
    A B
    1 blah..blah..KEYWORD 11.1..blah..blah.. 11.1
    2 blah..blah..KEYWORD 0.1..blah..blah.. 0.1
    3 blah..blah..KEYWORD 100..blah..blah.. 100

    Thanks in advance for any help.
    xu9j2

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,264

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    03-12-2009
    Location
    Coyanosa, USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    Please see attached sample BEFORE and AFTER worksheets. Thanks.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365 Version 2202
    Posts
    6,124

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    With your search word in D1, this works for your example:

    =MID(A1,SEARCH(D$1,A1)+9,4)+0

  5. #5
    Registered User
    Join Date
    03-12-2009
    Location
    Coyanosa, USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    Thanks. It worked for the majority of the records.There were a few records where the keyword was ignored, and an #N/A was returned. I can do this manually now.

  6. #6
    Registered User
    Join Date
    03-12-2009
    Location
    Coyanosa, USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    I meant to write #VALUE! was returned, not #N/A.

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365 Version 2202
    Posts
    6,124

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    The keyword wouldn't have been ignored. The more likely scenario was that

    =MID(A1,SEARCH(D$1,A1)+9,4)

    returned a non-numeric value. Adding 0 to a non-numeric value will return the #VALUE! error. If you would like for us to resolve this issue, please share a few examples of your data where the formula returned an error.

    Otherwise, if you are happy with the provided solution, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  8. #8
    Registered User
    Join Date
    03-12-2009
    Location
    Coyanosa, USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    Please see attachment.

    Column C indicates whether the keyword ("capacity") appears in column A cells. If the keyword does not appear in column A, an #N/A is displayed (see cell C3).
    Column B shows the numerical values returned by the formula. Column D shows the keyword.
    In rows 3, 5, 6, 9, 10, and 11, the formula should have returned numerical values, because the keyword was present in column A cells and it immediately preceded the numerical values.
    The formula worked for rows 2, 4, 8, 12, and 13.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-12-2009
    Location
    Coyanosa, USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    "The formula worked for rows 2, 4, 8, 12, and 13." -- the formula also worked for row 1: keyword is absent in cell A1, therefore #VALUE! was returned in cell B1

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365 Version 2202
    Posts
    6,124

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    Try this in B1:

    =IFERROR(MID(A1,SEARCH("capacity",A1,1)+8,SEARCH("GPM/",A1,1)-SEARCH("capacity",A1,1)-8)+0,NA())

  11. #11
    Registered User
    Join Date
    03-12-2009
    Location
    Coyanosa, USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    It worked. Thanks a bunch.

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365 Version 2202
    Posts
    6,124

    Re: Search for keyword in cell and return the numbers following the keyword in adjacent ce

    You're welcome. Thanks for the rep!

+ 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 cells C1:C3000 for keyword and if exists place a keyword in A1:A3000
    By GregQuick in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-24-2015, 07:38 PM
  2. Replies: 6
    Last Post: 01-13-2015, 12:35 PM
  3. Replies: 6
    Last Post: 03-18-2014, 11:16 AM
  4. [SOLVED] Formula to search from keyword list and return adjacent cell
    By Ninja2k in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2013, 08:44 AM
  5. Re-useability:Search a keyword and copy adjacent cell to in another sheet
    By illustratum in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-21-2013, 06:54 PM
  6. [SOLVED] Need to chck if a keyword is present in a text string and return keyword if yes
    By Jekaterina in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-16-2012, 05:55 PM
  7. Replies: 1
    Last Post: 12-27-2010, 08:53 AM

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