How to get and split specific number from value

Hi Naldi 

At first, go to the power query editor. then follow the instructions below 

Step 01: Change the Data Type number to text 

Click Add Column and then select Custom Column and pest the code 

= Table.TransformColumnTypes(#"Changed Type",{{"col", type text}})

 Output:

Step 02: Split first match 

Click Add Column and then select Custom Column and pest the code, then change type to text

= Table.AddColumn(#"Changed Type1", "split_col_1", each if Text.Contains([col], "122") then Text.Middle([col], Text.PositionOf([col], "122"), 6) else null)

 Output: 

Step 03: Split second match 

Click Add Column and then select Custom Column and pest the code, then change type to text

= Table.AddColumn(#"Changed Type4", "split_col_2", each 
    let
        textToSearch = [col],
        startPos = Text.PositionOf(textToSearch, "122"),
        secondStartPos = if startPos >= 0 then Text.PositionOf(Text.Middle(textToSearch, startPos + 3), "122", Occurrence.First) + startPos + 3 else -1
    in
        if secondStartPos >= 0 then Text.Middle(textToSearch, secondStartPos, 6) else null
)

Output:

Step 04: Split third match 

Click Add Column and then select Custom Column and pest the code, then extract the list and change data type to text

= Table.AddColumn(#"Changed Type2", "split_col_3", each Table.AddColumn(#"Changed Type4", "third", each 
    let
        textToSearch = [col],
        startPos = Text.PositionOf(textToSearch, "122"),
        secondStartPos = if startPos >= 0 then Text.PositionOf(Text.Middle(textToSearch, startPos + 3), "122", Occurrence.First) + startPos + 3 else -1,
        thirdStartPos = if secondStartPos >= 0 then Text.PositionOf(Text.Middle(textToSearch, secondStartPos + 3), "122", Occurrence.First) + secondStartPos + 3 else -1
    in
        if thirdStartPos >= 0 then Text.Middle(textToSearch, thirdStartPos, 6) else null
)[third])

Output:

Regards
Royel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.