How to Split Long Text into Rows and Columns in Excel Using TEXTSPLIT + WRAPROWS

If you’ve ever received a long line of data like this in Excel:

CopyEditRam-39,Male,Haryana, Sita-32,Female,Uttar Pradesh, Mohan-45,Male,Rajasthan, ...

…and wanted to split it into a clean, readable table like:

Name	Age	Gender	Location
Ram	39	Male	Haryana
Sita	32	Female	Uttar Pradesh
Mohan	45	Male	Rajasthan

— then this tutorial is for you!

Thanks to Excel 365 and Excel 2021, we now have two amazing functions: TEXTSPLIT and WRAPROWS, which make this process super easy.

🔧 Step-by-Step: The Formula

Assume the full data string is in cell B2, and you want to split it into rows with 4 columns (Name, Age, Gender, Location).

✅ Use this formula:

=WRAPROWS(TEXTSPLIT(B2,{",","-"}), 4)

---

🧠 Explanation

Let’s break it down:

1️⃣ TEXTSPLIT(A1,{",","-"})

This splits the text in A1 at each comma (,) and hyphen (-).
Result: a flat list like this –
Ram, 39, Male, Haryana, Sita, 32, Female, Uttar Pradesh, …

2️⃣ WRAPROWS(..., 4)

This takes the flat list and wraps it into rows of 4 items each, forming a neat table structure.

---

📌 Output Table

Name	Age	Gender	Location
Ram	39	Male	Haryana
Sita	32	Female	Uttar Pradesh
Mohan	45	Male	Rajasthan
…	…	…	…

---

⚠️ Important Notes

• This formula works only in Excel 365 and Excel 2021.
• For older Excel versions, use Power Query or VBA (we’ll cover that in another post).
• Make sure the data follows a consistent pattern (Name-Age,Gender,Location) for each user.

---

🎬 Want to See It in Action?

📺 Watch the full video tutorial here:
👉 Click to watch on YouTube

---

✍ Final Thoughts

This trick can save you tons of time when dealing with messy, unstructured data. With just one formula, you can transform a chaotic line into a well-organized dataset — perfect for analysis, reports, or just better readability.

Have any Excel questions or want to see this in Google Sheets or Power Query? Let us know in the comments or contact us directly!

---

🔗 Stay Connected

🌐 Website: https://quickinfoz.com
📧 Email: quickinfozchannel@gmail.com
📱 YouTube: QuickInfoz Channel

---

#ExcelTips #TEXTSPLIT #WRAPROWS #QuickInfoz #ExcelTutorials #DataCleaning #ProductivityHacks