This post will explain that how to split a cell into two or more cells within a single column. How do I convert one cell to multiple cells or rows in Excel. How to split the contents of a cell into multiple adjacent cells in with Text to Columns feature or with VBA code in Excel.

For examples, you want to split the text string of text in one single cell B1 into multiple rows in one single columns C. just do the following tutorial.

1. Split One Cell into Multiple Cells with Text to Columns Feature

#1 Select the Cell B1 that contains the text you want to split.

#2 go to Data tab, click Text to Columns command under Data Tools group.

#3 the Convert Text to Columns Wizard window will appear. Choose Delimited if it is not already selected, and then click Next button.

#4 select the delimiter or delimiters to define the places where you want to split the cell content. Check Semicolon, and clear the rest of the boxes under Delimiters section. Or check Comma and space if that is how you text is split. You can see a preview of you data in the Data preview section. Click Next button.

#5 click the Destination button to the right of the Destination box to collapse the dialog box. Select one Cells in your workbook where you want to paste your split data. Such as: D1, then click Finish button.

#6 you will see that the cell content has been split into multiple columns.

If you want to the split data can be separated into multiple cells in a single column, just continuing the next steps.

#7 select the split cells and right click on them, then click Copy menu from the drop down menu list. Or just press Ctrl +C short cuts.

#8 select Cell C1 that you want to past your split data, and right click on it. Then select Transpose (T) under Paste Options section.

#9 let’s see the last result.

2. Split One Cell into Multiple Cells with VBA Macro

You can also write an Excel VBA Macro to convert one cell into multiple cells or rows in a single column, just do the following steps:

#1 click on “Visual Basic” command under DEVELOPER Tab.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub splitOneCellIntoMore()
    Dim R As Range
    Dim I As Range
    Dim O As Range
    wTitle = "splitOneCellIntoMoreCells"
    Set I = Application.Selection.Range("B1")
    Set I = Application.InputBox("Select the Cell B1 that contains the text you want to split:", wTitle, I.Address, Type:=8)
    Set O = Application.InputBox("Select destination cell you want to paste your split data:", wTitle, Type:=8)
    A = VBA.Split(I.Value, ";")
    O.Resize(UBound(A) - LBound(A) + 1).Value = Application.Transpose(A)
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 select one cell that you want to split, such as: B1, Click OK button.

#7 select one destination cell that you want to paste your split data, such as: C1, click OK button.

#8 Let’s see the result.

3. Video: Split One Cell into Multiple Cells

This Excel video tutorial, we’ll explore two methods to split one cell into two or more cells. We’ll start by using the Text to Columns feature, followed by leveraging VBA code.

4. Related Examples

• Split Text String by Specified Character in Excel
  you should use the Left function in combination with FIND function to split the text string in excel. And we can refer to the below formula that will find the position of “-“in Cell B1 and extract all the characters to the left of the dash character “-“.=LEFT(B1,FIND(“-“,B1,1)-1).…
• Split Multiple Lines from a Cell into Rows
  how to split multiple lines from a cell into separated rows or columns in Excel. You will learn that how to extract text string separated by line break character into rows in excel 2013..…