Split Text by Line Break Like a Pro! - Power Query Challenge 75
How to Split Text by Line Break in Excel Power Query - Power Query Challenge 75
Ever seen a single cell packed with multiple values — separated by line breaks?
Like subject scores crammed together in one column?
It looks like this:
Math: 48
English: 54
Physics: 77
Religion: 64
It’s messy.
And trying to manually copy-paste each line? No thanks. 😅
In this Power Query tutorial, I’ll show you how to split line-break-separated text into clean rows, extract the numbers, and even find the highest score — all without writing a single formula.
📌 In This Video, You’ll Learn:
Step 1: Load your messy Excel table into Power Query
We’ll start with one column that holds multiple subject scores in a single cell.
Step 2: Keep only the useful parts
Using Text.Select, we remove everything except digits and line breaks.
Step 3: Split by line break
We use Text.Split(..., "#(lf)") to turn that one block of text into a list — each subject on its own line.
Step 4: Convert text to numbers
We use List.Transform to change "48", "54", etc. into real numbers.
Step 5: Find the max score
Finally, we apply List.Max to grab the top score from that list — one number per student.
✅ Key Takeaways:
✔️ How to split Excel text by line breaks using Power Query
✔️ Why Text.Split and List.Transform are perfect for cleaning up messy columns
✔️ A simple way to extract numeric values and calculate max scores
✔️ A clean, repeatable method — no formulas, no VBA, no manual cleanup
💬 Have you ever received Excel data like this — all jammed into one cell?
What’s your go-to trick for cleaning it up?
👇 Drop your favorite Power Query move in the comments — or ask me anything about this technique!
👀 And if you want more real-world Power Query tips, make sure to subscribe.
New challenges coming every week — and you won’t want to miss them.
Explore more tutorials:
🎥 Power Query Challenges Playlist:
• Power Query: Challenges
🎥 Dynamic Array Formulas Playlist:
• Modern Excel Challenges
#PowerQuery #ExcelTips #HowToLearnExcel
Links:
Challenge Author: Crispo Mwangi
LinkedIn: / crispo-mwangi-6ab49453
Youtube: @crispexcel
Website: https://www.crispexcel.com/
Original Challenge Post: / crispo-mwangi-6ab49453_excel-excelmastery-...
Solution: https://docs.google.com/spreadsheets/...