+ Reply to Thread
Results 1 to 10 of 10

Find partial text string within another text string return original text into cell.

  1. #1
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    191

    Find partial text string within another text string return original text into cell.

    Hi.

    I need to match column C with column A and return the original text string in column C into column B. I highlighted what the matches should look like and put a couple of example results in column B.
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Find partial text string within another text string return original text into cell.

    Try this in B2 and filled down

    =IFERROR(LOOKUP(2^15,SEARCH($C$2:$C$32,A2),$C$2:$C$32),"")

    Note, there cannot be any blanks in C2:C32

  3. #3
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Find partial text string within another text string return original text into cell.

    Hi mikey!

    Try this.. too..

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    @ Jonmo1..
    Are you hidding somewhere behind me.. did you copy my formula..
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  4. #4
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    191

    Re: Find partial text string within another text string return original text into cell.

    Quote Originally Posted by Jonmo1 View Post
    Try this in B2 and filled down

    =IFERROR(LOOKUP(2^15,SEARCH($C$2:$C$32,A2),$C$2:$C$32),"")

    Note, there cannot be any blanks in C2:C32
    This worked but when i upgraded the C column to include more variables some of the results came back as just the word Golf which i highlighted in green in this sheet.
    Attached Files Attached Files

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Find partial text string within another text string return original text into cell.

    The string in say A4 contains +golf
    so therefore it also contains golf
    Right?

    The function is returning the last item from C2:C44 that it found within the string in A4 (golf)

    You need to arrange your values in C2:C44 in order of priority when dealing with more than 1 matching value.
    If you would prefer to see +golf instead of just golf, then put the +golf AFTER golf.


    Hope that helps.

  6. #6
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    191

    Re: Find partial text string within another text string return original text into cell.

    Ok, is there a way to make it match the exact string as a whole? So +golf is only +golf and not both +golf and golf? or another example will be A6 +golf +club would be the result im looking for as it matches [+golf +club] as a whole in column C. With over 100,000 rows in my main sheet it might not always be the last result.

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Find partial text string within another text string return original text into cell.

    Try

    =IFERROR(LOOKUP(2^15,SEARCH(" "&$C$2:$C$44&" "," "&A2&" "),$C$2:$C$44),"")

  8. #8
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    191

    Re: Find partial text string within another text string return original text into cell.

    OMG thank you sooooo sooooooo much ive been killing myself for 4 days trying to figure this out. Ill test it on the large sheet fingers crossed.

  9. #9
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2016
    Posts
    191

    Re: Find partial text string within another text string return original text into cell.

    Yep seems to be working, i have no clue how but thank you none the less.

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Find partial text string within another text string return original text into cell.

    Great, glad to help. Thanks for the feedback.

+ 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