+ Reply to Thread
Results 1 to 4 of 4

Function that will search partial text match and place a 1 in column

  1. #1
    Registered User
    Join Date
    03-05-2015
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    9

    Function that will search partial text match and place a 1 in column

    I'm a newbie, so please bear with me.

    I need to compare two cells and place a "1" in the cell that matches.

    $j$2 contains the text I want to match i.e. "62 Years 4 Months"

    Cells I4:I358 contains all the "Years, Months, and days" values I want to lookup i.e. "62 Years 4 Months 3 Days"

    I need to select the partial text that matches $J$2 . When a match is found, I need to place a "1" in the cell.

    I tried Vlookup($J$2&"*",$I4:$I358,1,false)
    =index($I$4:$I$358,match($J$2,1)

    No luck - Any help would be appreciated

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Function that will search partial text match and place a 1 in column

    Hi thebrin- Paste this in J4 and copy down:
    =IF(ISNUMBER(SEARCH($J$2,I4)),1,"")

    Row\Col
    I
    J
    2
    Match this --->
    62 Years 4 Months
    3
    MAIN TEXT
    Match?
    4
    65 Years 4 Months 24 Days
    5
    62 Years 4 Months 11 Days
    1
    6
    65 Years 4 Months 6 Days
    7
    62 Years 4 Months 26 Days
    1


    You didn't say what to do if the search text isn't found. This leaves it blank ("").

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 07-09-2017 at 02:40 PM.

  3. #3
    Registered User
    Join Date
    03-05-2015
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    9

    Re: Function that will search partial text match and place a 1 in column

    Thanks leelnich - works great...

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Function that will search partial text match and place a 1 in column

    You're most welcome. If completed, please go to Thread Tools up top and mark your thread as SOLVED. Thanks!-Lee

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

+ 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. [SOLVED] MATCH / INDEX partial text in a single column
    By millerjj22 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-26-2017, 09:57 AM
  2. Conditional formatting based on search of partial text column
    By SirBrock in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-12-2015, 09:49 AM
  3. Match partial text and to fill other column
    By henk400 in forum Excel General
    Replies: 1
    Last Post: 03-19-2015, 04:17 AM
  4. Replies: 3
    Last Post: 01-22-2015, 06:21 PM
  5. [SOLVED] Find the row number with a partial match to text in column A
    By zookeepertx in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-10-2014, 01:39 PM
  6. Search and copy based on partial text match
    By PIQalap in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-12-2014, 11:36 AM
  7. Replies: 4
    Last Post: 07-27-2013, 07:38 AM

Tags for this Thread

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