Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
jajangjaras
Regular Visitor

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

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

IMG_20250127_110810.jpg

 and I need to split it into rows like this

IMG_20250127_111446.jpg

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

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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 

View solution in original post

6 REPLIES 6
v-pbandela-msft
Community Support
Community Support

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
Community Support
Community Support

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
Super User
Super User

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
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors