how can I split text start with 1. or 2. or 3. and so on into rows

[jajangjaras]

I am using 365 MS office, I need to split text that start with 1. or 2. or 3. and so on into rows. I have data like this

 and I need to split it into rows like this

 

I tried to split it by using line feed but it does not work because some cells contain line feed but the next row does not start with the pattern 1. or 2. or 3. and so on. Please help

[v-pbandela-msft]

Hi @jajangjaras,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @jajangjaras,

Thank you for reaching out in Microsoft Community Forum.

Thank you @slorin  for the helpful response.

As suggested by  Storin,  I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[slorin]

Comme je vois des mots en français dans votre copie d'écran, ce sera plus simple pour moi de répondre dans cette langue
Ce n'est pas une formule Excel calssique mais une requête Power Query (l'objet de ce sous-forum).
Vous chargez vos données dans Power Query (menu récupérer et transformer), vous collez le code dans une nouvelle requête vierge en remplaçant "Your_Table" par le nom de ce tableau.
Vous trouverez beaucoup de ressources sur internet pour apprendre les bases de Power Query

Stéphane

[slorin · Accepted solution]

Hi @jajangjaras 

let
Source = Excel.CurrentWorkbook(){[Name="Your_Table"]}[Content],
Replace_lf = Table.ReplaceValue(Source, null, null,
    (x,y,z)=> Splitter.SplitTextByCharacterTransition({"#(lf)"}, {"2".."9"})(x),
    {"Spare", "int", "com"}),
Transform = Table.FromColumns(
    List.Transform(
         Table.ToColumns(Replace_lf),
         List.Combine),
    Table.ColumnNames(Replace_lf))
in
Transform

Stéphane 

[jajangjaras]

thank you so much, it worked. But I did not add it directly in advanced editor, I applied to code as a new column and it solved the problem. 

[jajangjaras]

May I ask where can I write this code? and "Your_Table"...does it mean I should replace it with my table name? if so where can I see my table name? Thank you so much in advance.