Parse text and reformat output

[Alphacsulb]

In cell C1 I have the follow string:
O:\Data\Search Requests\2019\20190107 - Christine_Smith


I'm looking for a way to get the date 20190107 in cell A1.

Cell A1 is date formatted so it needs to recognize it as a date, not sure if the only way to do that is 01/07/2019.

Maybe a search starting at the right getting everything from the first number to the first \, and then output it as 01/07/2019?

 

[gaz_chops]

Maybe

=DATE(MID(C1,FIND(" -",C1)-8,4),MID(C1,FIND(" -",C1)-2,2),MID(C1,FIND(" -",C1)-4,2))

 

[Eric W]

Another option:

=DATEVALUE(REPLACE(REPLACE(MID(C1,FIND(" -",C1)-8,8),7,0,"/"),5,0,"/"))

Like gaz_chops, it relies on finding " -" to locate the date part. If that can occur elsewhere, we may need to change the formula.

 

[Alphacsulb]

Another option:

=DATEVALUE(REPLACE(REPLACE(MID(C1,FIND(" -",C1)-8,8),7,0,"/"),5,0,"/"))

Like gaz_chops, it relies on finding " -" to locate the date part. If that can occur elsewhere, we may need to change the formula.

This appears to work!

 

[Alphacsulb]

Maybe

=DATE(MID(C1,FIND(" -",C1)-8,4),MID(C1,FIND(" -",C1)-2,2),MID(C1,FIND(" -",C1)-4,2))

This also works! But it is throwing out 07/01/2019 instead of 01/07/2019

 

[sandy666]

another way

 

[gaz_chops]

As i use the "proper" date format as used in England

=DATE(MID(C1,FIND(" -",C1)-8,4),MID(C1,FIND(" -",C1)-4,2),MID(C1,FIND(" -",C1)-2,2))