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 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.

Excel VBA delimit Cell value into rows 02

 

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

Excel VBA delimit Cell value into rows 03

 

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.

Excel VBA delimit Cell value into rows 04

 

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

Excel VBA delimit Cell value into rows 07

 

Excel VBA delimit Cell value into rows 05

 

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

Excel VBA delimit Cell value into rows 06

 

Now you get the same result.

Excel VBA delimit Cell value into rows 04

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *