Extracting year from text string

[Ducklings23]

Hi all. I'm trying to extract the year (so four digit number) from various strings of text. The text may or may not contain other numbers. For context - worksheet is a list of incident reports and in some instances the date is contained within the description of the incident. I need to break the year out. Most of the time it just say things like "it was in November or late 2015" etc. I just want to extract any 4 digit number from the text, then I can check if it is a year or not afterwards. The year can appear in any part of the text string. Hope that makes sense! Thanks in advance.

 

[Ducklings23]

Perhaps a user-defined function like this might help? To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function FourDigits(s As String) As String
  Dim M As Variant
 
  With CreateObject("VBSCript.RegExp")
    .Global = True
    .Pattern = "(^|\D)(\d{4})(?=\D|$)"
    For Each M In .Execute(s)
      FourDigits = FourDigits & ", " & M.Submatches(1)
    Next M
  End With
  FourDigits = Mid(FourDigits, 3)
End Function

Ducklings23.xlsm
	A	B
1	it was in November or late 2015	2015
2	It was 2015 or 2014 in November	2015, 2014
3
4	cbcb
5	23659 abc 2345	2345
6	2012	2012
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=FourDigits(A1)

This worked perfectly - thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Ducklings23]

You're welcome. Thanks for the follow-up.

Hello again Peter, would you mind talking me through what the code you provided is doing in each step? I'm trying to learn so any help would be appreciated.

 

[bebo021999]

Alternative formula
Assum year in text string always after, i.e, year 2000
There is no other strange value exceed 2000

Book1
	A	B	C
1	blah blah 2000 blah		2000
2	blah blah 2021 blah		2021
3	blah blah15/11/2021blah		2021
4	blah 1500 blah2021blah		2021
5	blah blahblah		No date
6	blah blah 200 blah		No date
Sheet2

Cell Formulas
Range	Formula
C1:C6	C1	=IFERROR(AGGREGATE(14,6,(MID(SUBSTITUTE(A1,"/","|"),ROW($1:$100),4)+0)/(MID(A1,ROW($1:$100),4)+0>1999),1),"No date")

 

[Peter_SSs]

would you mind talking me through what the code you provided is doing in each step?

Regular Expressions are not easy to give simple explanations for. You would need to do some research on them.
The best I think that I can say is that the 'Pattern' "(^|\D)(\d{4})(?=\D|$)" looks for
(^|\D) The beginning of the string OR a non-digit
followed by
(\d{4}) exactly 4 digits
then looks ahead to check that the next character is
(?=\D|$) either a non-digit OR the end of the string

Then if that pattern is found, return the blue part (the 4 digits)

Alternative formula

Try your formula with these strings

Ducklings23.xlsm
	A
1	My code in 2015 was 23E4
2	It was 1DEC2015
Sheet2