Split Text to Rows in Excel (3 Easy Ways)

In this video, I will show you three ways to split text into rows in Excel. While Text to Columns is an everyday need, many times, you may also want to split the text in a cell into separate rows.
This can be done easily thanks to some awesome formulas in Excel and VBA.
00:00 Intro
00:46 Split Text to Rows using TEXTSPLIT function
06:42 Split Text to Rows using Text to Columns and Transpose
09:10 VBA to Split Text to Rows in Excel
---------------------------------------------------------------------------------
Below is the VBA code used in this video
'Code developed by Sumit Bansal from trumpexcel.com
Function SplitCellToRows(CellValue As Range, Delimiter As String)
Dim SplitValues() As String
'Split the value by the specified delimiter into an array
SplitValues = Split(CellValue.Value, Delimiter)
'Go through each element of the array and remove any leading or trailing spaces
For i = LBound(SplitValues) To UBound(SplitValues)
SplitValues(i) = Trim(SplitValues(i))
Next i
'Return the array
SplitCellToRows = WorksheetFunction.Transpose(SplitValues)
End Function
---------------------------------------------------------------------------------------
☕ If you find my Excel videos useful and would like to support me, you can buy me a coffee - www.buymeacoffee.com/SumitB
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-c...
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
✅ Best Excel Books: trumpexcel.com/best-excel-books/
Subscribe to get awesome Excel Tips every week: czcams.com/users/trumpexc...
#Excel #ExcelTips #ExcelTutorial

[@trumpexcel]

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.
Also, I have made all of my Excel courses available for free. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course

[@jagatkrishna1543]

Thanks ❤🙏 SIR

[@girishpadshala9689]

Thank you, & vba code function.

[@michaellundsrensen2292]

I know Textsplit but you gave me new knowledge with your good examples. Thanks!

[@trumpexcel]

Glad the video gave you some ideas. If you do use TextSplit in any creative way, would love to know about it

[@kryss8302]

❤ Thank you!!

[@IvanCortinas_ES]

Brilliant explanation. Grandmaster Sumit Bansal. Thank you!!

[@trumpexcel]

You are very kind my friend... Thank you for all the comments :)

[@mohsinraza4453]

Accept love from Pakistan you are doing so so so well 💓

[@trumpexcel]

Thanks so much for the kind words Mohsin... I am glad you are finding the videos useful ❤

[@Joseph-xt2qg]

You are an excellent teacher. Thanks!

[@trumpexcel]

Thank you so much for the kind words Joseph... Glad you are finding the videos useful :)

[@Satwant2463]

Thank you so much for this lesson. Highly useful.

[@trumpexcel]

Glad you found the video useful :)

[@neccomputercenter458]

very useful for anybody. Thank you !

[@trumpexcel]

Glad you found the video useful :)

[@nadermounir8228]

Thank you trump for this nice video especially the VBA part. U can also use filterXML along with the transpose function if u don't have the textsplit in your version of excel.

[@trumpexcel]

Glad you found the video useful... will check out the FilterXML method :)

[@da_ta]

Excellent thank you

[@trumpexcel]

Glad you found the video useful :)

[@rajsuthar999]

👌 indeed useful 🎉

[@trumpexcel]

Thanks... Glad you found the video useful :)

[@xeeshanahmad8757]

Hello Sir, Great To see you

[@trumpexcel]

Thanks for commenting... Good to be back on YT and making videos again :)

[@formylove5972]

sir mere excel me ye textsplit wala formula h hi nhi. or mujhe ye ache se smjhaaya tha av kya kru

[@michaellundsrensen2292]

How can I learn VBA from the beginning? I did code in Bacic and Pascal many years a go, and I make Macros and modify them slightly.

[@trumpexcel]

You can check out this free VBA course - czcams.com/play/PLm8I8moAHiH2n5HC4ZXBgS-cBLjxWDreu.html

[@michaellundsrensen2292]

@@trumpexcel Thanks, Ill do

[@triciaevans7303]

How to you split them into rows if there are rows already occupied with data underneath?

[@subhabratade93]

mere liye spit ho nehi raja. sirf 1st word split ho raha hain.kaha par galti kar raha hun? Please help Sumit ji..

[@trumpexcel]

Can you share the formula here? Also, I am assuming you are using M365 and have TEXTSPLIT function in your version of Excel.

[@Bikinigirl05]

THANK YOU SO MUCH!!!!

[@trumpexcel]

Glad you found the video helpful 🙂