Extracting Certain Numbers for a String

ib2013

New Member
Joined
Mar 17, 2016
Messages
2
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi ib2013, welcome to the MrExcel board.

Assuming it's always year 2000+

Code:
=MID(A1, FIND(2, A1), 4)
 
Upvote 0
Or the following arary formula, confirm with Crtrl+Shift+Enter
Code:
=SUM(IFERROR(--MID(SUBSTITUTE(A1," ","|"),ROW(INDIRECT("1:"&LEN(A1)-3)),4),0))

Preruiqisite: no period (.) or minus sign (-) immediately surrounding the 4 digits.
 
Upvote 0
Give this UDF (user defined function) a try...
Code:
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
 
Upvote 0
@ 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
 
Last edited:
Upvote 0
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 :-)
 
Upvote 0
@ 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.
 
Upvote 0
... 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. :diablo:
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top