Parse year from text

I would add a space as a prefix for each value, and then use Text.BetweenDelimiters, using " 2" and " " as delimiters. Just don't forget to go back and delete the leading " " in a later step!

 

--Nate

One possible approach is to split the text on any non-digit into a list and then take the first element of the list that has four digits.

 

Example query you can paste in to the Advanced Editor of a new Blank Query and examine the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5BCoMwEEWv8nFdQbNpuwzjmAaaSdApKOL9r+FoIWu3b94f3rY1rnM9ZAUvipK02R8n658gDigeE48/GS5MJbRfb8d36zqMkxf6xJmhfjmnplIeooQqpxn2/pYqdEu9yqwqCuX0dyRrJDa4Jpba/4JKbd8P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    NonDigits = Text.Remove(Text.Combine({" ".."~"}), {"0".."9"}),
    #"Added Custom" = Table.AddColumn(Source, "Result", each
        List.First(List.Select(Text.SplitAny([Text], NonDigits), each Text.Length(_) = 4)),
        type text)
in
    #"Added Custom"

 

How this works:

NonDigits is the string of characters from " " to "~" with the digit characters removed:

 !"#$%&'()*+,-./:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~

 Splitting the string "May 12th 2021 NY" on these characters gives a list:

Text.SplitAny("May 12th 2021 NY", NonDigits)
    = {null, null, null, null, 12, null, null, 2021, null, null, null}

Filtering (List.Select) for the elements of this list that are four characters long returns a list with a single element.

{2021}

 List.First gives the first element of this list or returns a null if the list is empty.