How to know if a string contains a date string

hcova

New Member
Joined
Jul 29, 2010
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
HI there.
I need to know if a date string (any date format) is part of a longer string. The problem is that the date string position can change its position in the longer one, so you can not use the left(), right() excel funtion to retrieve it. Moreover the date format separator can change too. For example "2021/08/26" or "2021-08-26".

For instance, I have the following string between " ":

"D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:12/08/2021^F.T.:2021/06/12^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:"

As you can see there is a Date String (dd/mm/yyyy) embedded in this string. In this case the Date String is 12/08/2021 as it is shown in red below:

"D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:12/08/2021^F.T.:2021/06/12^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:"

QUESTION 1: Is there any way to know if the Date String is embedded in the long string? With Excel functions or with a VBA code
QUESTION 2: Once identified if exist the Date String in the longer one, how can I read?

Any help is welcome.
Best regards
Hernán
 

hcova

New Member
Joined
Jul 29, 2010
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
HI there.
I need to know if a date string (any date format) is part of a longer string. The problem is that the date string position can change its position in the longer one, so you can not use the left(), right() excel funtion to retrieve it. Moreover the date format separator can change too. For example "2021/08/26" or "2021-08-26".

For instance, I have the following string between " ":

"D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:12/08/2021^F.T.:2021/06/12^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:"

As you can see there is a Date String (dd/mm/yyyy) embedded in this string. In this case the Date String is 12/08/2021 as it is shown in red below:

"D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:12/08/2021^F.T.:2021/06/12^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:"

QUESTION 1: Is there any way to know if the Date String is embedded in the long string? With Excel functions or with a VBA code
QUESTION 2: Once identified if exist the Date String in the longer one, how can I read?

Any help is welcome.
Best regards
Hernán
Dear Excelmates:

Yesterday I found a singular solution. The only restriction is that the Date String must have a "/" on it. You can improve it adding both "-" and "." date separators. Let me explain it with an example.
A1 has the following string (between " ")

"ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-2021/08/20^Code:MASSO VIRTUAL^N.H.:3^F.I.:2021/08/20^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:"

=TEXT(IF(FIND("/";A1)+3=FIND("/";A1;FIND("/";A1)+1);MID(A1;FIND("/";A1)-2;10));"yyyy/mm/dd")

You will get the first date 2021/08/20

I hope it will help.

Best regards
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,858
Office Version
  1. 365
Platform
  1. Windows
"ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-2021/08/20^Code:MASSO VIRTUAL^N.H.:3^F.I.:2021/08/20^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:"

=TEXT(IF(FIND("/";A1)+3=FIND("/";A1;FIND("/";A1)+1);MID(A1;FIND("/";A1)-2;10));"yyyy/mm/dd")

You will get the first date 2021/08/20
Actually, you don't. You get 21/08/20^C
 

hcova

New Member
Joined
Jul 29, 2010
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Actually, you don't. You get 21/08/20^C
Hi Peter SSs:
You are right. I am sorry for my mistake.
My formaul approach works when the Date String is with "dd/mm/yyyy" format only.

Let me explain with the same (modified) example I wrote:

"ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-20/08/2021^Code:MASSO VIRTUAL^N.H.:3^F.I.:20/08/2021^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:"

=TEXT(IF(FIND("/";A1)+3=FIND("/";A1;FIND("/";A1)+1);MID(A1;FIND("/";A1)-2;10));"yyyy/mm/dd")

Now you will get 2021/08/20

Regards
Hernán
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,858
Office Version
  1. 365
Platform
  1. Windows
So, are you all sorted now?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,993
Office Version
  1. 2007
Platform
  1. Windows
Moreover the date format separator can change too. For example "2021/08/26" or "2021-08-26"

As you can see there is a Date String (dd/mm/yyyy)

You can improve it adding both "-" and "." date separators.

My formaul approach works when the Date String is with "dd/mm/yyyy" format only.

The following solution, extracts the first date, either in the format "dd/mm/yyyy" or "yyyy/mm/dd". Contain "/" or "-" or "."
In column B put the cell format "yyyy/mm/dd"

If you have a problem, then you will need to provide several examples, that is, one example for each scenario you have.
Below I show you 7 cases:

Dante Amor
AB
1ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-2021/08/20^Code:MASSO VIRTUAL^N.H.:3^F.I.:2021/07/03^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:"2021/08/20
2ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-20/08/2021^Code:MASSO VIRTUAL^N.H.:3^F.I.:03/07/2021^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:2021/08/20
3ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-2021-08-20^Code:MASSO VIRTUAL^N.H.:3^F.I.:2021/07/03^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:"2021/08/20
4ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-20-08-2021^Code:MASSO VIRTUAL^N.H.:3^F.I.:03/07/2021^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:2021/08/20
5ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-2021.08.20^Code:MASSO VIRTUAL^N.H.:3^F.I.:2021/07/03^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:"2021/08/20
6ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-20.08.2021^Code:MASSO VIRTUAL^N.H.:3^F.I.:03/07/2021^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:2021/08/20
7ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-^Code:MASSO VIRTUAL^N.H.:3^F.I.:^F.T.:^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID: 
Hoja4
Cell Formulas
RangeFormula
B1:B7B1=IFERROR(SUBSTITUTE(IF(OR(MID(A1,SEARCH("202?",A1)+4,1)={"/","-","."}),MID(A1,SEARCH("202?",A1),10), MID(A1,SEARCH("/202?",SUBSTITUTE(SUBSTITUTE(A1,".","/"),"-","/"))-5,10)),".","/")+0,"")
 

hcova

New Member
Joined
Jul 29, 2010
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
The following solution, extracts the first date, either in the format "dd/mm/yyyy" or "yyyy/mm/dd". Contain "/" or "-" or "."
In column B put the cell format "yyyy/mm/dd"

If you have a problem, then you will need to provide several examples, that is, one example for each scenario you have.
Below I show you 7 cases:

Dante Amor
AB
1ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-2021/08/20^Code:MASSO VIRTUAL^N.H.:3^F.I.:2021/07/03^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:"2021/08/20
2ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-20/08/2021^Code:MASSO VIRTUAL^N.H.:3^F.I.:03/07/2021^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:2021/08/20
3ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-2021-08-20^Code:MASSO VIRTUAL^N.H.:3^F.I.:2021/07/03^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:"2021/08/20
4ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-20-08-2021^Code:MASSO VIRTUAL^N.H.:3^F.I.:03/07/2021^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:2021/08/20
5ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-2021.08.20^Code:MASSO VIRTUAL^N.H.:3^F.I.:2021/07/03^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:"2021/08/20
6ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-20.08.2021^Code:MASSO VIRTUAL^N.H.:3^F.I.:03/07/2021^F.T.:03/08/2021^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID:2021/08/20
7ABcD.:Course Virtual Pbyr Media of tooms Streaming!^-^Code:MASSO VIRTUAL^N.H.:3^F.I.:^F.T.:^N.A.:2^N.I.:0^V.C.:20000^T.D.:O/C 3737^ID: 
Hoja4
Cell Formulas
RangeFormula
B1:B7B1=IFERROR(SUBSTITUTE(IF(OR(MID(A1,SEARCH("202?",A1)+4,1)={"/","-","."}),MID(A1,SEARCH("202?",A1),10), MID(A1,SEARCH("/202?",SUBSTITUTE(SUBSTITUTE(A1,".","/"),"-","/"))-5,10)),".","/")+0,"")
Hello DanteAmor.
Thanks a lot for your formula. I am amazed how all of you can contribute for this issue.
Warm regards.
Hernan
 

Forum statistics

Threads
1,148,371
Messages
5,746,305
Members
424,006
Latest member
Metal_warrior

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top