CODEWITHSUNDEEP
excelexcel-formula
FrugalDonut
FrugalDonut

Reputation: 3

Extracting the second last date from a string

I would like to apply a formula that will extract the second last date of a string. Below example of strings:

DA1234621222 Corp|0|3|;2;1;3;8;01/01/2024;2;20000.00;2;2;1000000.00;|15|11/30/2023|
US12356HNM19 Corp|0|3|;2;4;3;1;04/29/2024;2;21000.000;2;.000;5;10/29/2024;2;21000.000;2;.000;5;04/29/2025;2;21000.000;2;.000;5;10/29/2025;2;2100.000;2;1000000.00;|15|10/31/2023|

In the first string, the second last date is 01/01/2024 and for the second string it is 10/29/2025. Hope someone can advise, thanks in advance.

I tried to use Find function but was unable to come up with a formula with locate the second last date.

Upvotes: 0

Views: 267

Answers (5)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

In Excel 2010 for a formula, you may try this:

First create a named formula named seq which will generate an array of numbers from 1 to 255

Formulas=>Define Name

=ROW(INDEX($A:$A,1):INDEX($A:$A,255))

enter image description here

Then use this formula in A1, for example, and fill down

=INDEX(DATEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"|",";"),";",REPT(" ",99)),IF(seq=1,1,seq*99),99))), AGGREGATE(14,6,ISNUMBER(DATEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"|",";"),";",REPT(" ",99)),IF(seq=1,1,seq*99),99))))*seq,2))

or formatted differently:

=INDEX(
    DATEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1, "|", ";"), ";", REPT(" ", 99)), IF(seq = 1, 1, seq * 99), 99))),
    AGGREGATE(
        14,
        6,
        ISNUMBER(DATEVALUE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1, "|", ";"), ";", REPT(" ", 99)), IF(seq = 1, 1, seq * 99), 99)))) *
            seq,
        2
    )
)

Data
enter image description here

Results
enter image description here

Be sure to format the result as a Date. Otherwise you may see a 5 digit number.

Algorithm

  • Replace each delimiter with 99 spaces
  • Create an array of values using the MID function with a start number of 1,99,198...
    • Trim each entry in the array
  • The DATEVALUE function should convert each element that looks like a date into a date, otherwise it should return an error
  • ISNUMBER will then convert this into an array of row numbers or zero's to find the index into the array for the actual dates
  • AGGREGATE will return the penultimate element index that contains a date
  • INDEX into the array using the index to return the penultimate date

Upvotes: 0

Manoj
Manoj

Reputation: 461

I got the answer without spill array, you just need to drag the formula.

Result

=MID(C3,CHOOSECOLS(UNIQUE(IFERROR(FIND("/",C3,SEQUENCE(,LEN(C3))),""),1),1,3)-2,10)

I have taken 1st & 2nd dates in my formula, If you need only 2nd date, try this one.

=MID(C3,CHOOSECOLS(UNIQUE(IFERROR(FIND("/",C3,SEQUENCE(,LEN(C3))),""),1),3)-2,10)

Result2

Upvotes: 0

cranky
cranky

Reputation: 59

Vba Code

 Function LastDate(rng As Range, Optional Number=1)
    Dim lenstring, s As Integer
    lenstring = Len(rng)
    
    s = 0
    For i = lenstring To 1 Step -1
        If Mid(rng, i, 1) = "/" Then
            s = s + 1
            If s = Number * 2 Then
                LastDate = Mid(rng, i - 2, 10)
                Exit Function
            End If
        End If
    Next i
End Function

Use

=LastDate(A1,2)

Upvotes: 1

P.b
P.b

Reputation: 11628

=LET(t,TEXTSPLIT(A1,{"|",";"}),CHOOSECOLS(FILTER(t,ISNUMBER(FIND("/",t))),-2))

First we declare t the textsplit of A1 by delimiters | and ;.

t is filtered for only values containing / and CHOOSECOLS takes the second last string. (If you add -- in front of CHOOSECOLS it will be converted from the string to an actual value, which you could format as a date)

If a non-date string could potentially consist a / you could use =LET(t,TEXTSPLIT(A1,{"|",";"}),CHOOSECOLS(FILTER(t,ISNUMBER(SEARCH("??/??/????",t))),-2))

Upvotes: 2

Gaurav Singh
Gaurav Singh

Reputation: 1

So far, I think if you use Split function with delimiters semi-colon[;] and pipe[|], your task can be easier.

Upvotes: 0

Related Questions