What is wrong my formula?

[azizrasul]

Trying to extract a date in any part of a cell. However I can't get the following formula to work.

=IFERROR(DATEVALUE(LEFT(RIGHT(A2,LEN(A2)-FIND("/",A2)-3))),10)),"")

 

[DanteAmor]

try

varios 31ene2024.xlsm
	A	B
1
2	Trying to extract a date 31/01/2024 in any part of a cell	31/01/2024
3	Trying to extract a date in any part of a cell
Hoja4

Cell Formulas
Range	Formula
B2:B3	B2	=IFERROR(DATEVALUE(MID(A2,FIND("/",A2)-2,11)),"")

 

[azizrasul]

I just get a blank. No errors though.

 

[DanteAmor]

Can you give some examples of what you can put in the cell and what do you expect as a result?

As you can see, my example works.

You must provide more information, it is not enough to put:

I just get a blank. No errors though.

 

[azizrasul]

Actually it kinda worked. Here is what I get. I tried it on the second cell at random and I got a blank as you can see. If I use 10/05/2018, then it works.

Financial Statements dated 31/12/2018	31/12/2018​
Deposits on 10/5/2018 were 25 million
I was born on 2/2/1958	02/02/1958​
When were you born?
You can come and join us on 01/01/2021 for training	01/01/2021​

 

[Peter_SSs]

Try this and format the formula column with your preferred date format.

24 02 01.xlsm
	A	B
1
2	Financial Statements dated 31/12/2018	31/12/2018
3	Deposits on 10/5/2018 were 25 million	10/05/2018
4	I was born on 2/2/1958	02/02/1958
5	When were you born?
6	You can come and join us on 01/01/2021 for training	01/01/2021
Extract Date

Cell Formulas
Range	Formula
B2:B6	B2	=LET(t,TEXTSPLIT(A2," "),IFERROR(--FILTER(t,ISNUMBER(FIND("/",t))),""))

 

[Alex Blakenburg]

If you need it to work on all the versions of Excel you have listed, maybe

=IFERROR(--TRIM(MID(A2,FIND("/",A2)-2,10)),"")

 

[SunnyAlv]

try with VBA

Sub FindDate()

    Dim RegEx As RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strOutput As String
    Dim objMatches As Object
    Dim i As Long
    Dim rng As Range
    
    On Error Resume Next

    Set RegEx = New RegExp
    RegEx.Global = True
    RegEx.IgnoreCase = True

    'Set the RegEx pattern to find the date string within the input string
    RegEx.Pattern = "\b\d{1,2}[\/-](\d{1,2}|(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec))[\/-](\d{4}|\d{2})\b"
    
    
    'Set the range of input string cells
    Set rng = Range("A1:A5")

    For i = 0 To rng.Count - 1
        strInput = Range("A1").Offset(i, 0).Value
    
        ' Find all matches of pattern in input string
        Set objMatches = RegEx.Execute(strInput)

        ' Returning only the first value found in the input string
        strOutput = objMatches(0).Value
        Range("A1").Offset(i, 1).Value = strOutput
    Next i
    

End Sub

 

[Peter_SSs]

If you need it to work on all the versions of Excel you have listed, maybe

=IFERROR(--TRIM(MID(A2,FIND("/",A2)-2,10)),"")

Don't know if it could be possible with the OP's data but that would fail if the date is the first thing in the cell & the date had a single-digit day value.
TEXTSPLIT method already handles that but if required, that circumstance could be overcome with your method as shown below in col D

@azizrasul
Again I don't know if it would ever be possible with your data but the TEXTSPLIT formula should also work if any of the dates in the text have 2-digit years instead of 4. eg row 8 below

24 02 01.xlsm
	A	B	C	D
1
2	Financial Statements dated 31/12/2018	31/12/2018	31/12/2018	31/12/2018
3	Deposits on 10/5/2018 were 25 million	10/05/2018	10/05/2018	10/05/2018
4	I was born on 2/2/1958 ss	02/02/1958	02/02/1958	02/02/1958
5	When were you born?
6	You can come and join us on 01/01/2021 for training	01/01/2021	01/01/2021	01/01/2021
7	2/2/1999 is Tom's DoB	02/02/1999		02/02/1999
8	Deposits on 10/5/18 were 25 million	10/05/2018
Extract Date

Cell Formulas
Range	Formula
B2:B8	B2	=LET(t,TEXTSPLIT(A2," "),IFERROR(--FILTER(t,ISNUMBER(FIND("/",t))),""))
C2:C8	C2	=IFERROR(--TRIM(MID(A2,FIND("/",A2)-2,10)),"")
D2:D8	D2	=IFERROR(--TRIM(MID(" "&A2,FIND("/"," "&A2)-2,10)),"")

 

[azizrasul]

When I use B2

=LET(t,TEXTSPLIT(A2," "),IFERROR(--FILTER(t,ISNUMBER(FIND("/",t))),""))

it comes up with an error "That function isn't valid". It highlights FILTER.

C2 and D2 works for 4 digit years but not 2 digit years as stated above.

SunnyAlv, thanks for that. Will try it in the future, ideally looking for a formula solution at the moment.