How to replace specific text search for any date search

**katiedee1625** · 10-08-2018, 09:39 AM

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:

Please Login or Register  to view this content.

Thanks!
Katie

**FlameRetired** · 10-08-2018, 10:40 AM

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?

**Glenn Kennedy** · 10-08-2018, 10:41 AM

=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.

**katiedee1625** · 10-08-2018, 11:01 AM

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!!

**katiedee1625** · 10-08-2018, 11:17 AM

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!

**Glenn Kennedy** · 10-08-2018, 11:22 AM

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.

How to replace specific text search for any date search

LinkBack

Thread Tools

Rate This Thread

Display

How to replace specific text search for any date search

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Search through columns and find specific text and replace that text with header column

VBA Code - Search text & search number & search qty and result - Urgent Please

[SOLVED] Replace search results with a specific Word

Search and Replace a specific character

[SOLVED] search for text, then search for previous text, replace 1st text including number from 2nd

[SOLVED] search for text in colB, starting with next row search for 1st occurrence of specific #

Search for specific text in a string and replace

Bookmarks

Bookmarks

Posting Permissions