Checking string of certain format

[Syndicate_Admin]

Hey Everyone, I am new to power query and this has had my head scratching is there anyway I can solve this?

Is it possible to check a string dynamically where AA****...BB where AA  and BB are alphabets while ****... represents any amount of integers. If that is possible i would like to return it to an new column

eg:                                Output

AB123456BC 3033       AB123456

AB1234BC 2022           AB1234

[AlB]

Hi there

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQyNDI2MTVzclYwNjA2VorVgYkBRYwMjIyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1 - Copy.1", "Column1 - Copy.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Column1 - Copy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1 - Copy.1", "Output"}})
in
    #"Renamed Columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Vijay_A_Verma]

Use this in a custom column where Input is column name

= Splitter.SplitTextByCharacterTransition({"0".."9"},{"A".."Z", "a".."z"})(Text.Split([Input]," "){0}){0}

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQyNDI2MTVzclYwNjA2VorVgYkBRYwMjIwgIoZOzi5uSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Splitter.SplitTextByCharacterTransition({"0".."9"},{"A".."Z", "a".."z"})(Text.Split([Input]," "){0}){0})
in
    #"Added Custom"

[Syndicate_Admin]

Hey Thanks, this works but there is a small issue.

Sometimes the records are as such 

AB1234/01BC

AB1234-01BC

how do i extract the above for that type of records

[Vijay_A_Verma · Accepted solution]

Use this

= Splitter.SplitTextByCharacterTransition({"0".."9"},{"A".."Z", "a".."z"})(Text.SplitAny([Input],"/-"" """){0}){0}