Split a column into two separate columns

[Mederic]

Hello,

I have a data range of approximately 2,000 rows, each containing the supplier number followed by the name.

The table is structured using this same logic throughout.

I would like to transform this column into two columns with the number and the name.

I have two methods that work, but I would like to optimise them with fewer steps.

If possible, I would also like to see one or two other methods suggested.

I am attaching a small sample file.

Thanks in advance

Best regards

[Mederic]

Hello @OwenAuger , @AlienSx ,

Thank you for your solutions, which work very well.
Indeed, Table.Split and List.Split are very useful here.
I slightly prefer the code below from AlienSx Thank you for your solutions, which work very well.
Indeed, Table.Split and List.Split are very useful here.
I slightly prefer the code below from AlienSix, even though the other codes are also very good.
Thank you.
Have a nice day.
Best regards.:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.FromList(
        List.Split(Table.ToList(Source, (x) => Text.AfterDelimiter(x{0}, " ")), 2),
        (x) => x, 
        {"Supplier", "Name"}
    )
in
    result

Thank you.
Have a nice day.
Best regards.

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(
        Source, 
        "List", 
        {"fx", (x) => Record.FromTable(Table.SplitColumn(x, "List", Splitter.SplitTextByEachDelimiter({" "}), {"Name", "Value"}))}, 
        GroupKind.Local, 
        (s, c) => Number.From(Text.StartsWith(c, "Supplier"))
    ),
    z = Table.FromRecords(group[fx])
in
    z

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    result = Table.FromList(
        List.Split(Table.ToList(Source, (x) => Text.AfterDelimiter(x{0}, " ")), 2),
        (x) => x, 
        {"Supplier", "Name"}
    )
in
    result

[OwenAuger]

Hi @Mederic 

Here's one suggestion:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}),
    #"Split List" = List.Split(#"Changed Type"[List],2),
    #"Extract Text" = List.Transform(#"Split List", each { Text.AfterDelimiter(_{0},"Supplier "), Text.AfterDelimiter(_{1},"Name ") } ),
    #"Convert to Table" = Table.FromList(#"Extract Text", each _, type table[Supplier = text, Name = text])
in
    #"Convert to Table"

 List.Split is used to partition the list into pairs.

Depending on the source, there might be some need to ensure original row order is preserved when the list is split.