+ Reply to Thread
Results 1 to 6 of 6

How to replace specific text search for any date search

  1. #1
    Registered User
    Join Date
    07-29-2016
    Location
    dallas, tx
    MS-Off Ver
    365
    Posts
    51

    How to replace specific text search for any date search

    I have been using the following formula that looks for a "yes" on CompanyOne worksheet F4:F160. If a "yes" was in the cell, it would return the text from the corresponding cell B4:B160, which is a client name. However, the "yes" is now being replaced with a date on that sheet. I want to update the formula to look for any date, which is formatted as 00/00/0000, instead of the "yes." I've tried replacing the "yes" with a wildcard of "*" and also ">0", but it will not return any results. What am I doing wrong?

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



    Thanks!
    Katie

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,747

    Re: How to replace specific text search for any date search

    Check to make sure your dates are actual dates and not text that looks like dates.

    If the dates are text "10/8/2018" they have a numeric value of 0.

    You can test those dates to see. Assuming one of the dates is in cell Company!F8 in an unused cell enter this formula =ISNUMBER(Company!F8). If it is a date it will return TRUE. If not you will need to convert or reenter those as actual dates.

    Has this helped?
    Dave

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,596

    Re: How to replace specific text search for any date search

    =IFERROR(INDEX(COMPANYONE!$B:$B,AGGREGATE(15,6,ROW(COMPANYONE!$B$4:$B$160)/(ISNUMBER(COMPANYONE!$F$4:$F$160)),ROWS(A$2:A2))),"")

    You can safely use a whole column reference for the INDEX part, removing a couple of intemediate steps later on.

    Red: this bit will check for the presence of a number and will exclude any text/blank cells.

    Green: by convention this is usually the first cell of the column used for displaying the results. I had the formula in A2.

    Then simply copy downwards.
    Glenn



  4. #4
    Registered User
    Join Date
    07-29-2016
    Location
    dallas, tx
    MS-Off Ver
    365
    Posts
    51

    Re: How to replace specific text search for any date search

    Yes! I went back and checked and saw I only had part of the column formatted as dates, with the rest as general. (It pays to double-check your work). I've made the change, updated my formula and it works perfectly!

    Thank you!!

  5. #5
    Registered User
    Join Date
    07-29-2016
    Location
    dallas, tx
    MS-Off Ver
    365
    Posts
    51

    Re: How to replace specific text search for any date search

    Thank you so much for your help! I've made the changes and it works perfectly! I appreciate the detailed explanation as it helps me to learn.

    Now on to the next challenge of this ever-changing workbook of 20 plus worksheets!

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,596

    Re: How to replace specific text search for any date search

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  2. VBA Code - Search text & search number & search qty and result - Urgent Please
    By naresh73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2014, 10:51 AM
  3. [SOLVED] Replace search results with a specific Word
    By zicitron in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-30-2013, 06:25 AM
  4. Search and Replace a specific character
    By VegasL in forum Excel General
    Replies: 6
    Last Post: 12-09-2012, 01:26 PM
  5. [SOLVED] search for text, then search for previous text, replace 1st text including number from 2nd
    By kldailey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-06-2012, 05:23 PM
  6. [SOLVED] search for text in colB, starting with next row search for 1st occurrence of specific #
    By roothog in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2012, 10:39 PM
  7. Search for specific text in a string and replace
    By VegasL in forum Excel General
    Replies: 5
    Last Post: 12-04-2009, 04:45 PM

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