Splitting text into separate column with inconsistent delimter

If your Raw Data encompasses the actual possible patterns, then the following will work:

Raw Data

Code (inserted after the step producing the above screen shot):

    #"Added Custom" = Table.AddColumn(#"Previous Step", "Split", (r)=> 
        let 
            Split=Text.Split(r[#"Company item "],"_"),
            LastTwo = Text.Combine(List.LastN(Split,2),""),
            Test = (try Number.From(LastTwo))[HasError]=true,
            SplitRest = if Test then r[#"Company item "] else List.RemoveLastN(Text.Split(r[#"Company item "],"_"),2),
            Mid = if Test then null else List.Last(SplitRest),
            First = if Test then SplitRest else Text.Combine(List.RemoveLastN(SplitRest),"_")
        in 
            {First, Mid, if not Test then LastTwo else null}, type {text}),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Split", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Split", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Split.1", "Split.2", "Split.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Company item ", type text}, {"Split.1", type text}, {"Split.2", Int64.Type}, {"Split.3", Int64.Type}})
in
    #"Changed Type"

Results:

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    split = Table.ToList(
        Source, 
        (x) => Splitter.SplitTextByCharacterTransition({"_"}, (x) => Text.Contains("0123456789", x))(x{0})
    ),
    tbl = Table.FromList(
        split, 
        (x) => 
            [trim = List.Buffer(List.Transform(x, (w) => Text.Trim(w, "_"))),
            out = {trim{0}, trim{1}?, trim{2}? & trim{3}?}][out]
    )
in
    tbl

Hi mukhan311, 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcox3ineOtzSLNzUwNDGIN1SK1YEJuqALR8RHxqflFykEeBkZmRuZGyjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company item" = _t]),
    Ad_Helper = Table.AddColumn(Source, "Splitted", each 
        [ a = Splitter.SplitTextByCharacterTransition({"_"}, (x)=> List.Contains({"0".."9"}, x))([Company item]),
          b = List.Transform(List.FirstN(a, 3), (x)=> Text.Trim(x, {"_", " "})),
          c = Text.Combine(b, "||")
        ][c], type text),
    #"Split Column by Delimiter" = Table.SplitColumn(Ad_Helper, "Splitted", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv))
in
    #"Split Column by Delimiter"