Extracting Certain Numbers for a String

[ib2013]

Hi,

I have a set of string that contain different years at different parts of the string. There is no other occurence of a 4 digit numeric value in the string and I wondered if its possible just to extract the date. Example below

A Random Wine 2012
2013 Another Wine

Either VBA or Formula would work

Any help would be appreciated

 

[dave3009]

Hi ib2013, welcome to the MrExcel board.

Assuming it's always year 2000+

=MID(A1, FIND(2, A1), 4)

 

[MarcelBeug]

Or the following arary formula, confirm with Crtrl+Shift+Enter

=SUM(IFERROR(--MID(SUBSTITUTE(A1," ","|"),ROW(INDIRECT("1:"&LEN(A1)-3)),4),0))

Preruiqisite: no period (.) or minus sign (-) immediately surrounding the 4 digits.

 

[Rick Rothstein]

Give this UDF (user defined function) a try...

Function GetYear(S As String) As Long
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(" " & S & " ", X, 6) Like "[!0-9]####[!0-9]" Then
      GetYear = Mid(S, X, 4)
      Exit Function
    End If
  Next
End Function

 

[ib2013]

Wow, thanks all for the quick responses. I'll give them a go!

 

[Peter_SSs]

@ Dave & Marcel
Not sure what is possible with the OP's data, but it doesn't state that there are no other digits in the string beside the 4-digit string of interest.
Each of your formulas return incorrect results for these strings.

A 21E1 Random Wine 2012
Top2Decks Shiraz 2012

 

[dave3009]

That's a good point, I had taken the "There is no other occurence of a 4 digit numeric value" a little too broadly I think

 

[MarcelBeug]

@ Dave & Marcel
Not sure what is possible with the OP's data, but it doesn't state that there are no other digits in the string beside the 4-digit string of interest.
Each of your formulas return incorrect results for these strings.

A 21E1 Random Wine 2012
Top2Decks Shiraz 2012

You're right; at the same time you deliberately chose a scientific number (21E1) and a date (2Dec). Only in these specific case my formula returns a wrong result.

 

[Peter_SSs]

... you deliberately chose ...

Of course you are correct Marcel, but formulas that can produce incorrect results without warning can be a significant problem so I was raising the issue - just in case.

If the user was expecting "recent" years then the result for the example I gave above would most likely stand out & be picked up, but in the event that buried in the data was "A .2E1 Random Wine 2013" (very unlikely I admit ) then the 2015 resulting from that string would most likely go unnoticed.

 

[MarcelBeug]

Completely agree Peter.

My formula in post #3 should not be used. Just to let other readers know why not. That should be clear now.