Parse year from text

[lipster26]

Hello,

Looking for the best way to parse the year from cells with text. The years at the beginning of the text could be done with splitting into columns, but how about the cells where the year is in the middle of the text? Appreciate the help. thanks.

Text	Result
2021 NY EXT PMT	2021
2017 CEG PA REFUND	2017
CPG-LA2019-20 FRANCHISE TAX PMT RECODING	2019
CPG-MS 2020 FRANCHISE TAX PMT RECODING	2020
CPG-NC 2020 FRANCHISE TAX PMT RECODING	2020
2017 PA INCOME TAX NOTICE PAYMENT	2017
2018 TN REFUND	2018

[rohit_singh · Accepted solution]

Hi @lipster26 

Crude but effective one-liner

= Table.AddColumn(#"Changed Type", "Year", each Number.From(Text.Range([Text],Text.PositionOf([Text], "20",Occurrence.First),4)))

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[AlexisOlson]

Nice. This is one of those problems that can be done many ways but the more robust you need the calculation to be, the more complex the solution has to be. Sometimes quick and dirty is good enough.

[AlexisOlson]

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.

[watkinnc]

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