Extract date from text string in Excel

[jameson222]

Hi,
I have a problem where i try to extract dates from a string of texts in Excel. The problem i have is that the dates have slightly different formats.
Example 1: MM/DD/YYYY (10/29/2019)
Example 2: M/D/YYYY (9/1/2019)

Below are examples of the text strings from which i need to extract and convert this to a proper Excel Date:
9CVD0001FECDS_TG1_QT10/29/2019 11:57 PM10/29/2019 11:57 PMFailed Activity
9CVD0029FECDS_TG1_QT9/1/2019 12:43 AM9/1/2019 1:35 AMMissed Activity

One helpful note is that the date always come after the letters "QT" (marked bold above).
Also, date appear in the string twice, i only need to extract the first date.

Can you advise on a formula that can manage to extract both formats? Thanks!!

 

[Toadstool]

Hi Jameson222,

Does this work for you?

Book1
	A	B
1	9CVD0001FECDS_TG1_QT10/29/2019 11:57 PM10/29/2019 11:57 PMFailed Activity	10/29/2019
2	9CVD0029FECDS_TG1_QT9/1/2019 12:43 AM9/1/2019 1:35 AMMissed Activity	9/1/2019
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=MID(A1,SEARCH("QT",A1)+2,SEARCH(" ",A1,SEARCH("QT",A1))-SEARCH("QT",A1)-2)

 

[Peter_SSs]

Welcome to the MrExcel board!

IF QT is in the same position every time as it is in your samples then try
=MID(LEFT(A1,FIND(" ",A1,21)),21,10)+0

Otherwise try
=REPLACE(LEFT(A1,FIND(" ",A1,FIND("_QT",A1))),1,FIND("_QT",A1)+2,"")+0

 

[jameson222]

Hi Jameson222,

Does this work for you?

Book1
	A	B
1	9CVD0001FECDS_TG1_QT10/29/2019 11:57 PM10/29/2019 11:57 PMFailed Activity	10/29/2019
2	9CVD0029FECDS_TG1_QT9/1/2019 12:43 AM9/1/2019 1:35 AMMissed Activity	9/1/2019
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=MID(A1,SEARCH("QT",A1)+2,SEARCH(" ",A1,SEARCH("QT",A1))-SEARCH("QT",A1)-2)

This worked perfect to do the correct extraction!
Perhaps basic question, but this is still recognized in the new cell as text. How do i best convert this to a date format? When i try =Datevalue function it doesnt work?

 

[sandy666]

an alternative

RAW		RAW
9CVD0001FECDS_TG1_QT10/29/2019 11:57 PM10/29/2019 11:57 PMFailed Activity		29/10/2019
9CVD0029FECDS_TG1_QT9/1/2019 12:43 AM9/1/2019 1:35 AMMissed Activity		01/09/2019

Power Query M-code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Date = Table.TransformColumnTypes(Table.TransformColumns(Source, {{"RAW", each Text.BetweenDelimiters(_, "QT", " "), type text}}), {{"RAW", type date}}, "en-US")
in
    Date

 

[MARK858]

Perhaps basic question, but this is still recognized in the new cell as text.

Try adding +0 to the end of the formula or test the formulas in post number 3.

 

[Peter_SSs]

Have you tried any of the other suggestions?

 

[jameson222]

Thank you everyone for the quick replies!! Thats greatly appriciated !

Toadstools formula in post 1 works great to extract both those dates as text. I have tried the formulas in post 3 but cant get either of them to work in my file... The adding +0 at the end of the formula didnt work either.

Somehow i thought the tricky part would be to get the formula to pick out the correct date from the text string considering the single and double digit month and day. But that worked fine, now i'm stuck at what i thought would be the easier bit to get that converted to a date :D

I havent tried the power query code yet. As a first step and based on the people who will be using and updating this report i would prefer to see if there is a way to get this working via regular formula.

Anyone more suggestions ?

 

[MARK858]

What does =ISNUMBER(B2) give you with Toadstool's formula (both with and without the +0)?
The + 0 works for me with Toadstool's formula, just have to format the cell as date.

 

[Peter_SSs]

I have tried the formulas in post 3 but cant get either of them to work in my file.

What do they do with those sample texts you gave?
- Give the wrong date? (What date?)
- Give an error? (What error?)
- Something else? (What?)

Do you have the same number of characters up to the "QT" (as I suggested with my first formula) or not?

What is the standard date format in your system? D/M/Y or M/D/Y or something else?