Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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
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.
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.
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
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
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.
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.