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.

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

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


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

Filter Data by Column and Sort by Row
filter by column sort by row1

This post will guide you how to use the FILTER function to filter a value by column and then sort data by row in Microsoft Excel. You can use the following formula based on the SORT function in combination with ...

Filter And Transpose Data From Horizontal To Vertical
filter and transpose horizontal to vertical1

This post will show you how to use Filter function and in combination with Transpose function to filter data from horizontal and transpose data as vertical in Microsoft Excel. You can refer to the below general formula based on TRANSPOSE ...

VLOOKUP Formula | Faster Trick with 2 VLOOKUPS
Faster VLOOKUP with 2 VLOOKUPS1

This post will guide you how to use 2 VLOOKUPS function to looking up data entries from a given range of cells in Microsoft Excel. VLOOKUP with 2 lookups can be faster than a single VLOOKUP in certain scenarios. The ...

Extract Unique Items From A List
Extract Unique Items From A List In Excel1

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the ...

Extract substring In Excel
Extract substring In Excel1

This post will guide you how to use Excel's MID function is a quick and easy way to extract pieces from your text. Use the Excel formula to extract a substring with MID. Note: If you want to extract just ...

How to Use 3D SUM Multiple Worksheets
3D sum multiple worksheets1

To sum a range of numbers is straightforward for most Excel users, but do you know how to establish a 3D reference to total the same range of numerous sheets. In this post, I will present the steps for this ...

Extract all the matches with helper Column
how to extract all matches with helper column1

With Excel's powerful functions IF, INDEX, and MATCH, we can find exactly what you're looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! ...

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

Sidebar