Results 1 to 4 of 4

Thread: Search for text within cell, then return contents of that cell

  1. #1

    Search for text within cell, then return contents of that cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    So right now I have a spreadsheet that looks something like this:

    A B C D
    APPLE INC XOM EXXON MOBILE CORP. =
    BERKSHIRE HATH-A BRK/A BERKSHIRE HATHAWAY
    WELLS FARGO & CO WFC WELLS FARGO & CO.
    GENERAL ELECTRIC GE GENERAL ELECTRIC CO.
    EXXON MOBIL CORP CVX APPLE INC.
    WAL-MART STORES WMT WAL-MART STORES CO.
    JPMORGAN CHASE JPM JPMORGAN CHASE & CO.


    As you can see, the stock names are slightly different in columns A & C (CORP. vs CORP, CO vs CO., etc). I need a formula in column D that searches column A for the first word in column C, and then retrieves the contents of that cell. I want to do this because I will then use a vlookup in column E to get the ticker for the stock.

    Right now I have:
    =IF(ISERROR(SEARCH(LEFT(C1,FIND(" ",C1)),A:A,1)),A:A,"")

    This formula searches column A for EXXON, but does not return the contents of the cell. Instead, it returns the contents of a different cell in the column.

    Anyone have insight on how to fix? Thanks!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,478
    Articles
    0
    Excel Version
    365
    Insert a new column between columns A and B, and in the new B1:
    =LEFT(A1,SEARCH(" ",A1)-1)
    Copy down.

    Then in E1:
    =VLOOKUP(LEFT(D1,SEARCH(" ",D1)-1),$B$1:$C$7,2,0)
    Copy down

  3. #3
    Great, excellent. Thanks!

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,478
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Excel_2014 View Post
    Great, excellent. Thanks!
    as if…

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •