How to know if a string contains a date string

H

hcova

New Member
Joined
Jul 29, 2010
Messages
19
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
 
Sort by date Sort by votes
hcova said:
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
Click to expand...
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
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
hcova said:
"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
Click to expand...
Actually, you don't. You get 21/08/20^C
 
Upvote 0
Peter_SSs said:
Actually, you don't. You get 21/08/20^C
Click to expand...
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
 
Upvote 0
hcova said:
Moreover the date format separator can change too. For example "2021/08/26" or "2021-08-26"
Click to expand...

hcova said:
As you can see there is a Date String (dd/mm/yyyy)
Click to expand...

hcova said:
You can improve it adding both "-" and "." date separators.
Click to expand...

hcova said:
My formaul approach works when the Date String is with "dd/mm/yyyy" format only.
Click to expand...

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,"")
 
Upvote 0
DanteAmor said:
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,"")
Click to expand...
Hello DanteAmor.
Thanks a lot for your formula. I am amazed how all of you can contribute for this issue.
Warm regards.
Hernan
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
397
Peter_SSs
Peter_SSs
V
  • Solved
Finding Price from Matching Date and Yarn ID.
2
Replies
13
Views
716
Vishnu75
V
S
  • Solved
IF function excluding 0 and 9 in text string
Replies
4
Views
148
dreid1011
dreid1011
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
1K
Alex Blakenburg
A
Romano_odK
  • Question
How can I do this cumulative?
Replies
1
Views
250
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,891
Messages
6,122,101
Members
449,066
Latest member
Andyg666

Share this page

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
Back
Top