Formula referencing cell(s) with dates as well as text inside them

[MustaphaOj]

Hi All,

I have a particularly tricky task as I have a big data set that I am sitting through looking for some dates. Sounds simple enough until
You see some are formatted like this
“14-09-20 A” while the rest are formatted like “14/07/2020”
My question is if I wanted to make a formula that searches the whole data set looking for dates after a certain date (let’s say 23/07/2029) WITH the “A” included how would I go about it?

Name	Date
Mark	14-09-20 A
John	15/07/2020
Peter	17-06-18 A

 

[CA_Punit]

is the Date prefixed by only " A" or there are some other character also in dates.

 

[gaz_chops]

Easiest way is to use Text to Columns, Highlight Column containing Dates, select Data>Text To Columns>Next>Chk "Space" as delimiter>Hit Finish

This will separate the data after the space into a separate column, leaving your dates as usable dates.

 

[MustaphaOj]

is the Date prefixed by only " A" or there are some other character also in dates.

Hi there,

it is only the letter “A”. In theory that should make it simpler?

 

[MustaphaOj]

Easiest way is to use Text to Columns, Highlight Column containing Dates, select Data>Text To Columns>Next>Chk "Space" as delimiter>Hit Finish

This will separate the data after the space into a separate column, leaving your dates as usable dates.

Hi Gaz,

Thank you for this. It’s a useful tool however I do need the dates with “A” in them as they are important to the task.

 

[gaz_chops]

OK. Then give more details as to what you want to do, is it simply to return a Yes/No if it finds the date? Or return other data or...........?

=left(B2,8)+0 can extract the date value, if that help

 

[MustaphaOj]

Yes that’s exactly it. If it finds a date with an “A” in it after for example 23/07/20 then return a “Yes” if it doesn’t then “No”

 

[gaz_chops]

Try

=IF((LEFT(SUBSTITUTE(B2," ",REPT(" ",99)),10)+0)>=$C$1,"Yes","No")
$C$1 = date 23/07/2029

 

[CA_Punit]

Just in case you need to replace it use Ctrl+F and Replace " A" with "" so you will be left with only dates and Keep the Text with Right function =Right(Cell reference,2) it will keep the Text Also

Use Helper column do it .

I hope it might help you