How to Split One Cell into Two or More Cells

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.

split one cell into multiple cells1

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

split one cell into multiple cells2

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

split one cell into multiple cells3

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

split one cell into multiple cells4

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

split one cell into multiple cells5

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.

split one cell into multiple cells6

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

split one cell into multiple cells7

#9 let’s see the last result.

split one cell into multiple cells8

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

split one cell into multiple cells9
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.

split one cell into multiple cells10

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

split one cell into multiple cells11

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

split one cell into multiple cells12

#8 Let’s see the result.

split one cell into multiple cells13

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.

https://youtu.be/XUJxDOVeQcE

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

Leave a Reply