Pull date from cell with multiple info

[surkdidat]

Is it possible to pull the date from a cell, which is typically like this

07/01/2021 13:13:46 - Status change: Complete
07/01/2021 13:13:40 - Status change: In QA - BPS
07/01/2021 13:13:36 - Status change: In configuration
07/01/2021 13:13:33 - Status change: Governed
04/01/2021 10:23:52 - Status change: In configuration
04/01/2021 10:23:26 - Status change: Governed
04/01/2021 10:04:40 - Status change: Submitted

I always want to pull the date from the "highest" row in the cell for Status change: In configuration

So, I would want to pull 07/01/2021 in this example.

Each line above is on a separate "row", but is in the same cell (for example N1)

 

[Fluff]

How about

Fluff.xlsm
	G	H
1
2	07/01/2021 13:13:46 - Status change: Complete 07/01/2021 13:13:40 - Status change: In QA - BPS 07/01/2021 13:13:36 - Status change: In configuration 07/01/2021 13:13:33 - Status change: Governed 04/01/2021 10:23:52 - Status change: In configuration 04/01/2021 10:23:26 - Status change: Governed 04/01/2021 10:04:40 - Status change: Submitted	07/01/2021
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LEFT(FILTERXML("<k><m>"&SUBSTITUTE(G2,CHAR(10),"</m><m>")&"</m></k>","//m[contains(.,'In configuration')][1]"),10)

 

[surkdidat]

Will the extract in cell H2 recognise that as a date? For instance, if I put a formula to count dates between 01/01/2023 and 31/01/2023 in please?

 

[Fluff]

If you need it as a date just add +0 to the end of the formula

 

[surkdidat]

You, sir (or madam!!) , are a genius

Just for my learning can i make it " [contains(.,'In configuration') " or [contains(.,'Off-shore configuration') ?

(Note, unfortunately, I cannot simply just use the word "configuration"

What are the K and m in the formulas doing please?

Thanks once again

If you need it as a date just add +0 to the end of the formula

 

[Fluff]

Yes, like

=LEFT(FILTERXML("<k><m>"&SUBSTITUTE(G2,CHAR(10),"</m><m>")&"</m></k>","//m[contains(.,'In configuration') or contains(.,'Off-shore configuration')][1]"),10)+0

 

[surkdidat]

G E N I U S ! !

I would like to know what the <k> an <m> in the formula do please?

Yes, like

=LEFT(FILTERXML("<k><m>"&SUBSTITUTE(G2,CHAR(10),"</m><m>")&"</m></k>","//m[contains(.,'In configuration') or contains(.,'Off-shore configuration')][1]"),10)+0

 

[surkdidat]

Apologies, one more question on this.... I can see the 10 is extracting the number of characters of the date. If this was an predetermined length of all characters before an "x", can this be adapted once more?

=LEFT(FILTERXML("<k><m>"&SUBSTITUTE(G2,CHAR(10),"</m><m>")&"</m></k>","//m[contains(.,'In configuration')][1]"),10)

 

[Fluff]

Can you post some sample data.

 

[surkdidat]

Can you post some sample data.

Something like

2x horses, 129x cats, 30x dogs, 18x white rhinos

So, instead of "In configuration" I would be looking at putting the animal (in this example) and would want the "quantity" before the "x"

With the animal names, I am debating on whether to use "Text to columns" to split these out in the row. An alternate would be using column headers

Initial data	Horses	Cats	Dogs	White Rhinos	African elephants	Indian elephants
2x horses, 129x cats, 30x dogs, 18x white rhinos	2	129	30	18
2x cats, 19x White Rhinos, 3260x African elephants, 1x Indian elephant		2		19	3260	1
45x Dogs			45