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

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

---------------------------------------------------------------------------------------

#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:

[marcusgenz]

I should worship for this, but I bow to man. 😂

[IvanCortinas_ES]

Brilliant explanation. Grandmaster Sumit Bansal. Thank you!!

[michaellundsrensen]

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

[Satwant]

Thank you so much for this lesson. Highly useful.

[mohsinraza]

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

[nadermounir]

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.

[sriprashanth]

Hi i am getting #Name error when i use this in vba
I copied the code in description in to a separate module but when i use it in workbook, it shows “#Name?” Error, my intent is to split 3, 4, 10, 5…. To rows
Can you help me

[subhabratade]

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

[michaellundsrensen]

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.

[triciaevans]

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

[formylove]

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