Excel VBA delimit Cell value into rows

This Excel tutorial explains how to delimit cell value into rows using VBA and non VBA.

You may also want to read:

Convert cell contents into table

Access Excel custom split Function to delimit text

Excel VBA separate line break data into different rows

Excel VBA delimit Cell value into rows

To demonstrate how to delimit Cell value into rows, I use email address as an example.

One day I tried to copy a list of email addresses from Outlook into Excel.

 

When I paste to Excel 2013, all email addresses are pasted to a single Cell A1.

 

Therefore I wrote a Macro to delimit a cell value into rows.

Press ALT+F11 > Insert a Module > copy and paste the below code in the Module

Public Sub delimitcell()
    Set targetRange = Range("A1")
    targetDelimiter = ";"
    Set newRange = targetRange.Offset(1, 0)
    
    For i = 0 To UBound(Split(targetRange, targetDelimiter))
        Set newRange = newRange.Offset(1, 0)
        newRange.Value = Split(targetRange, targetDelimiter)(i)
    Next
End Sub

Press F5 to run the Macro, and I got the list of email addresses in row.

 

Delimit Cell value into rows without VBA

If you don’t like VBA, you can use Text to Column function to delimit A1 by semicolon

 

 

Afterwards copy row 1 Cells and then use Transpose function to convert columns to rows

 

Now you get the same result.