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>

 

[jtakw]

Hi,

According to you description:

Book1
	A	B
1	BLS Instructor Renewal & Monitoring Registration - 2/28/2019 (LT-FW)	2/28/2019
2	BLS Instructor Renewal & Monitoring Registration - no date (LT-FW)
3	BLS Instructor Renewal & Monitoring Registration - 12345678 (LT-FW)
4	BLS Instructor Renewal & Monitoring Registration - 1/1/19 (LT-FW)	1/1/2019
5	BLS Instructor Renewal & Monitoring Registration - 12/31/19 (LT-FW)	12/31/2019
6	BLS Instructor Renewal & Monitoring Registration - 12/1/2019 (LT-FW)	12/1/2019
Sheet706

Cell Formulas
Range	Formula
B1	=IFERROR(LEFT(SUBSTITUTE(MID(A1,SEARCH("- */*/* ",A1)+2,99)," ",REPT(" ",99)),99)+0,"")

Result converted to Real Date, format result cell to Date display as desired.

 

[Rick Rothstein]

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.

I am confused. If the text is always the same format, how is it that you have to test for the presence of a date? The same format means (to me) that you have text, a dash, a date followed by some text in parentheses. If that is not the case, what does your text without a date look like (is the rest of the description I gave accurate)?

 

[bobkap]

Thanks for your message Mr. Rothstein. The string does not always have a date in it do I don't want my macro to bomb out if it comes to a cell without a date in it.

What I meant by "format" is that within the string the date is always formatted the same way.

Sorry for the confusion.

 

[Rick Rothstein]

Thanks for your message Mr. Rothstein. The string does not always have a date in it do I don't want my macro to bomb out if it comes to a cell without a date in it.

What I meant by "format" is that within the string the date is always formatted the same way.

To help in locating the date... is the text immediately after it always a space character followed by an open parenthesis followed by whatever?

 

[bobkap]

YES! I hadn't noticed that, but all the cells with a date do that. Not to push my luck, but I also need to keep the data in parenthesis but I was trying to solve the date issue first.

Thanks again for your help! Not sure if you recognize my name, but you've helped me before and I hope you can this time too!

 

[Rick Rothstein]

YES! I hadn't noticed that, but all the cells with a date do that. Not to push my luck, but I also need to keep the data in parenthesis but I was trying to solve the date issue first.

It is always best to ask for everything you need at once as it is easier to build a solution knowing the ultimate goal as opposed to building a solution for a partial question only to have to heavily modify it, or throw it away completely, once you ask the next part of your question. With that said, you addition has me looking at a different possible approach from the one I was originally thinking about. With that said, I have some additional questions.

1) Is the date and subsequent text always located after the last dash in the text?

2) Do you want the date and the text in parentheses both in the same cell or in separated cells?

3) If separate cells, do you want to retain the parentheses or remove them?

4) Is there any text located after the closing parentheses?

 

[bobkap]

1) Is the date and subsequent text *always* located after the last dash in the text? YES
Here are more samples of the string:
Red Cross First Aid with Adult CPR/AED Registration - 1/11/2019 (LT-FW)
HeartCode® ACLS Parts 2&3 (Hands on Skills) Registration - 3/12/2019 (LT-MV)
HeartCode® BLS for Healthcare Provider CPR Parts 2&3 (Hands on Skills) Registration - 3/12/2019 (LT-MV)


2) Do you want the date and the text in parentheses both in the same cell or in separated cells? Separate please


3) If separate cells, do you want to retain the parentheses or remove them? Remove them please.


4) Is there any text located after the closing parentheses? NO

 

[Peter_SSs]

1) Is the date and subsequent text *always* located after the last dash in the text? YES

That answer, and question too, seem a little unusual to me given that there is a dash as the 4th last character in all the samples.

I have a further question since you haven't given any samples where there is no date but have indicated that is possible.
Could the data be like rows 2 & 3 of jtakw's post where there are no dates but still the text in parentheses at the end? If so, do you want that text extracted even though there is no date?

Also, is it possible to have a date in the string without any subsequent text in parentheses?

 

[bobkap]

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