Date in string

[bobkap]

I have numerous records that include a cell that looks something like this:

BLS Instructor Renewal & Monitoring Registration - 2/28/2019 (LT-FW) The length of the data varies and is not one consistent length. However, it is always in this same format. I need to do the following: 1. Identify if there is a date in the string. 2. If there is a date in the string I need to copy just the date and write it in another cell. I can do the copying to another cell, I just cannot seem to figure out how to make sure the string has a date in it AND how to extract/copy JUST the date. Any help would be greatly appreciated!

<tbody>
</tbody>

 

[Rick Rothstein]

Thanks for asking. When there isn't a date in the cell it's just text. Here are samples below. If there are no dates, there are no parenthesis after. We only need the rows where there is a date following by a space and then parenthesis with text in it. (We'd prefer to delete the rows where there is not date followed by parenthesis. BUT, I think with my limited macro knowledge, I can figure that one out?) I hope this makes sense. Thanks again!

10 - HS Manuals, 1 - Manual, 3 - BLS Manuals - Picked up at class. RH
18-2926 and 18-2938
18-2954 & 19-2113
19-2015
19-2422, 19-2475, 19-2483
19-2426
19-2546
19-2690
2 Shirts
2 Shirts
3 HS Hard Copy Cards
4 Tees and 2 Logo Keys
ACLS and BLS online key and Skills 2/26
ACLS and BLS online key and skills August 28

Are you saying 2/26 and August 28 are not considered dates? In other words, in order to be a date, it must be three numbers separated by slashes, correct?

Also, you did not answer Peter's last question... (rephrased) can you have a date that is not followed by text in parentheses?

 

[TheRogue]

I don't have a full answer to the question, but to identify the text as a Date, couldn't you just use the VALUE() function?
=IF(AND(VALUE(B2>29221),VALUE(B2<228643)),[Function if True],"")

 

[Rick Rothstein]

I don't have a full answer to the question, but to identify the text as a Date, couldn't you just use the VALUE() function?
=IF(AND(VALUE(B2>29221),VALUE(B2<228643)),[Function if True],"")

Figuring out what a date is is not the problem... what Peter and I are trying to do is find a foolproof way of identifying if there is a date within the text and, if so, how to located its position within the text. See Message #8 for some examples of text containing dates along with Message #10 showing examples without a date within the text.

 

[bobkap]

Got it. If there is a date, it will always be with slashes in this format xx/xx/xxxx.

Thanks very much to you and Peter for all your help. Please let me know if I am not giving you the info you need.

Bob

 

[Rick Rothstein]

Please let me know if I am not giving you the info you need.

Peter asked it originally and I re-asked it, but you still have not answered this question...

Can you have a date that is not followed by text in parentheses?

 

[bobkap]

Gosh. Sorry. No. If there is a situation where there IS a date not followed by a parenthesis and some text within the parenthesis, it's a row of data I prefer to delete. We only need rows that have data with a date at the end of that cell followed by a space and then a parenthesis with text in it.

Thanks again for your help.

 

[Peter_SSs]

See if these user-defined functions work for you.
Note that my dates are in d/m/y format.

Function GetDate(s As String) As Variant
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  GetDate = vbNullString
  RX.Pattern = "(\d{1,2}\/){2}\d{4}(?= \()"
  If RX.Test(s) Then GetDate = CDate(RX.Execute(s)(0))
End Function

Function GetText(s As String) As String
  GetText = IIf(IsDate(GetDate(s)), Replace(Split(s, "(")(UBound(Split(s, "("))), ")", ""), "")
End Function

Excel Workbook
	A	B	C
1	Data	Date	Text
2	BLS Instructor Renewal & Monitoring Registration - 28/2/2019 (LT-FW)	28/2/2019	LT-FW
3	BLS Instructor Renewal & Monitoring Registration - no date (LT-FW)
4	BLS Instructor Renewal & Monitoring Registration - 12345678 (LT-FW)
5	BLS Instructor Renewal & Monitoring Registration - 1/1/2019 (LT-FW)	1/1/2019	LT-FW
6	BLS Instructor Renewal & Monitoring Registration - 1/12/2019 (LT-FW)	1/12/2019	LT-FW
7	Red Cross First Aid with Adult CPR/AED Registration - 11/1/2019 (LT-FW)	11/1/2019	LT-FW
8	HeartCode ACLS Parts 2&3 (Hands on Skills) Registration - 12/3/2019 (LT-MV)	12/3/2019	LT-MV
9	HeartCode BLS for Healthcare Provider CPR Parts 2&3 (Hands on Skills) Registration - 12/3/2019 (LT-MV)	12/3/2019	LT-MV
10	BLS Instructor Renewal & Monitoring Registration - 28/2/2019 dfgdgd
11	BLS Instructor Renewal & Monitoring Registration - 28/2/2019
12	BLS Instructor Renewal & Monitoring Registration - 31/12/2019 (LT-FW)	31/12/2019	LT-FW
Sheet1

If you want to get rid of the rows that have no dates in column B then you could try:

Sub RemoveUnwanted()
  Application.ScreenUpdating = False
  With Range("B1", Range("B" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:=""
    .Offset(1).EntireRow.Delete
    .AutoFilter
  End With
  Application.ScreenUpdating = True
End Sub