Excel tutorial: split text, number, and date cells (separate into multiple columns)

When using Excel, you may need to split text from one cell into multiple cells for some purposes. This tutorial is divided into three parts: split text cells, split number cells and split date cells. Each part provides different examples to help you know how to handle the splitting job when encountering the same problem.

1 Split text cells

This part collects the situations you will encounter when splitting a text cell into multiple columns, and provides the corresponding methods for you.

Example #1 Split cells by comma, space or other delimiter(s)

To split a text cell into multiple columns by a specific delimiter such as comma, space, dash and so on, you can apply one of the below methods.

Split cells by delimiter with the Text to Columns feature

The Text to Columns feature, as an Excel built-in, is frequently used in splitting cells. As shown in the screenshot below, to split cells in the Text Strings column by comma, you can apply the Text to Columns feature as follows.

1. Select the column range you want to split by comma, click Data > Text to Columns.

2. In the Convert Text to Columns Wizard – Step 1 of 3 dialog box, keep the Delimited radio button selected, and then click the Next button.

3. In the Convert Text to Columns Wizard – Step 2 of 3 dialog box, specify a delimiter according to your needs (in this case, I only check the Comma checkbox), and then click the Next button.

4. In the last step dialog box, click the button to select a cell to output the separated texts, and then click the Finish button.

Then texts in the selected range are split by a comma and placed in different columns as shown below.

Split cells by delimiter with formula

You can apply the below formula to split texts in cells by a specified delimiter in Excel.

Generic formula

=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

Arguments

Then go ahead to apply this formula.

1. As shown in the screenshot below, first, you need to create a helper row with the number 1, 2, 3... locating in different cells.

Note: Here 1, 2, 3... represent the first, second, and the third substring of the text string.

2. Select a cell under the number 1 cell, copy or enter the below formula into it and press the Enter key to get the first substring of the text string. Select the result cell, drag its AutoFill Handle right and down to get the other substrings. See screenshot:

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(D$4-1)*LEN($B5)+1,LEN($B5)))

Note: In the formula, “,” is the delimiter used to split the text string in B5. You can change it according to your needs.

Split cells by delimiter with an amazing tool

Here, we recommend the Kutools for Excel’s Split Cells feature to help you easily split cells into separate columns or rows by a certain delimiter in Excel.

1. After installing Kutools for Excel, select the range where you want to split the text strings, and then click Kutools > Merge & Split > Split Cells.

2. In the Split Cells dialog box, you need to configure as follows.

3. In the next Split Cells dialog box, select a cell to output the substrings, and then click OK.

Then substrings are split into different columns or rows based on your specified in step 2 above.

Split to columns:

Split to rows:

Example #2 Split cells by certain length

To split text string by certain length, the following methods can do you a favor.

Split cells by certain length with the Text to Columns feature

The Text to Columns feature provides a Fixed width option to help you split text strings in selected cells by certain length in Excel.

As shown in the screenshot below, to split the texts in range B5:B9 into columns every 3 characters, you can do as follows to get it done.

1. Select the cells where you want to split the text strings.

2. Click Data > Text to Columns.

3. In the Convert Text to Columns Wizard – step 1 of 3 dialog box, select the Fixed width radio button and click Next.

4. Then the Step 2 of 3 dialog box pops up. In the Data preview section, click at the desired position on the axis to create a break line (a line with arrow). After creating all break lines, click the Next button to go ahead.

In this case, I create break lines for every 3 characters in the text string.

5. In the last step wizard, select a cell to output the separated texts, and then click the Finish button.

Now the text strings in selected cells are split by every 3 characters as shown in the screenshot below.

Split cells by certain length with an amazing tool

To split cells by certain length in Excel, you can apply the Split Cells feature of Kutools for Excel to get it done easily.

1. Select the text string cells you want to split by certain length, click Kutools > Merge & Split > Split Cells.

2. In the Split Cells dialog box, you need to configure as follows.

3. In the next Split Cells dialog box, select a cell to place the splitting texts and then click OK.

Then the text strings in the selected cells are split by certain length and placed in different columns.

Example #3 Split cells by a certain word

As shown in the screenshot below, to split text strings in range B5:B9 by an entire word “sales”, you can apply the formulas provided in this section.

Get the substring before a certain word in a cell

Applying a formula based on the LEFT and the FIND functions can help to get the substring before a certain word in a text string.

Generic formula

=LEFT(A1,FIND("certain_word", A1)-1)

Arguments

1. Select a blank cell, copy or enter the below formula into it and press the Enter key to get the substring before the certain word. Select this result cell, and then drag its AutoFill Handle down to apply the formula to other cells.

=LEFT(B5,FIND("sales",B5)-1)

Get the substring after a word in a cell

After getting the substring before the word, you need to apply the below formula to get the substring after it.

Generic formula

=TRIM(MID(A1,SEARCH("certain_word",A1)+LEN("certain_word"),255))

Arguments

1. Select a blank cell next to the first result cell (D5).

2. Copy or enter the below formula into it and press the Enter key. Select this result cell, drag its AutoFill Handle down to get the other results.

=TRIM(MID(B5,SEARCH("sales",B5)+LEN("sales"),255))

Now you have split text strings into different columns by an entire word.

Example #4 Split cells by line break

This section demonstrates different methods to help you split text cells by line break in Excel.

Split cells by line break with the Text to Columns feature

The Text to Columns feature can be applied to split cells by line break in Excel. You can do as follows.

1. Select the range of cells where you want to split the texts by line break.

2. Click Data > Text to Columns.

3. In the Convert Text to Columns Wizard – Step 1 of 3 dialog box, choose the Delimited radio button and then click Next;

4. In the Step 2 of 3 dialog box, uncheck any existing Delimiters selections, check the Other checkbox, and then press the Ctrl + J shortcut. You can see that only a little dot is displayed in the textbox, then in the Data preview box, the texts are split by line breaks. Click the Next button.

5. In the last step wizard, select a destination cell to output the separated texts, and then click the Finish button.

Then texts in the selected cells are split into different columns by line breaks.

Split cells by line break with formula

The following formulas can also help to split cells by line break in Excel.

Using the same example as above, after splitting, you will get three substrings in different columns.

Get the substring before the first line break

Firstly, we can apply a formula based on the LEFT and the SEARCH functions to split the substring before the first line break in the cell.

Generic formula

=LEFT(cell, SEARCH(CHAR(10),cell,1)-1)

Arguments

1. Select a blank cell (D5 in this case), copy or enter the below formula into it and press the Enter key. Select the result cell and drag its AutoFill Handle down to get the substrings before the first line break of other cells.

=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)

Get the substring between the first and second line breaks

To get the substring between the first and second line breaks in a cell, the following formula can do you a favor.

Generic formula

=MID(cell,SEARCH(CHAR(10),cell)+1,SEARCH(CHAR(10),cell,SEARCH(CHAR(10),cell)+1)-SEARCH(CHAR(10),cell)-1)

Arguments

1. Select a cell (E5) next to D5, copy or enter the below formula into it and press the Enter key. Select the result cell and drag its AutoFill Handle down to get the substrings between the first and second line breaks of other cells.

=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)

Get the substring after the second line break

The first step is to get the substring after the second line break with the below formula.

Generic formula

=RIGHT(cell,LEN(cell) - SEARCH(CHAR(10),cell, SEARCH(CHAR(10), cell) + 1))

Arguments

1. Select a cell (F5 in this case), copy or enter the below formula into it and press the Enter key. Select the result cell and drag its AutoFill Handle down to get the substrings after the second line break of other cells.

=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) + 1))

Split cells by line break with VBA

This section provides a VBA code to help you easily split texts in selected cells by line break in Excel.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy the below VBA into the Code window.

VBA code: split cells by line break in Excel

Sub ExtendOffice_SplitRangeTex()
'Updated by Extendoffice 20211116
Dim xStr() As String
Dim xRg As Range
Dim xCell As Range
Dim xI As Integer
Set xRg = Application.InputBox("Please select the range of cells where you want to split by line break:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
For xI = 1 To xRg.Count
    Set xCell = xRg.Item(xI)
    xStr = VBA.Split(xCell.Value, vbLf)
    xCell.Resize(1, UBound(xStr) + 1).Offset(0, 1) = xStr
Next
End Sub

3. Press the F5 key to run the code. Then a Kutools for Excel dialog box pops up, you need to select the range of cells where you want to split by line break, and finally click the OK button.

Then texts in selected cells are split into different columns at once by line breaks.

Split cells by line break with an amazing tool

Here, we introduce a handy tool – Split Cells utility of Kutools for Excel. With this tool, you can split texts in selected cells in bulk by line break with only several clicks.

1. Select the range of cells where you want to split the texts by line break.

2. Click Kutools > Merge & Split > Split Cells to enable the feature.

3. In the Split Cells dialog box, you need to make the following settings.

4. In the next popping up Split Cells dialog box, select a blank cell to place the separated texts, and click OK.

Then texts in the selected cells are split into different columns by line breaks as shown in the screenshot below.

Example #5 Split cells by the first or last delimiter only

Sometimes, you may need to split a cells into two parts by first or last delimiter only, you can try the below formulas.

Split cells by the first delimiter with formulas

As shown in the screenshot below, to split each cell in range B5:B9 into two parts by the first space, you need to apply two formulas.

Split the substring before the first space

To split the substring before the first space, you can use a formula based on the LEFT function and the FIND functions.

Generic formula

=LEFT(cell,FIND("delimiter",cell)-1)

Arguments

1. Select a cell (D5 in this case) to output the substring, copy or enter the below formula into it and press the Enter key. Select the result cell and then drag its AutoFill Handle down to get the substring of other cells.

=LEFT(B5,FIND(" ",B5)-1)

Split the substring after the first space

Then apply the below formula to get the substring after the first space in a cell.

Generic formula

=RIGHT(cell,LEN(cell)-FIND("delimiter",cell))

Arguments

1. Copy or enter the below formula into the cell E5 and press the Enter key to get the result. Select this result cell and drag its AutoFill Handle down to get the substring after the last space of other cells.

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

Note: In the formulas, you can change the cell reference to the text string and the delimiter according to your needs.

Split cells by the last delimiter with formulas

To split texts in the range of cells (B5:B9) into two parts by the last space as shown in the screenshot, the two formulas provided in this section can help you get it done.

Get the text to the left of the last delimiter

To get the text to the left of the last delimiter in a cell, you can apply the below formula.

Generic formula

=LEFT(B5,SEARCH("^",SUBSTITUTE(cell,"delimiter","^",LEN(cell)-LEN(SUBSTITUTE(cell,"delimiter",""))))-1)

Arguments

1. Select a cell to place the left substring, copy or enter the below formula into it and press the Enter key. Select the result cell and drag its AutoFill Handle down to apply the formula to other cells.

=LEFT(B5,SEARCH("^",SUBSTITUTE(B5," ","^",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)

Get the text to the right of the last delimiter

Now we need to get the text to the right of the last space in this case.

Generic formula

=TRIM(RIGHT(SUBSTITUTE(cell,"delimiter",REPT("delimiter",LEN(cell))),LEN(cell)))

Arguments

1. Select a cell to place the right substring, copy or enter the below formula into it and press the Enter key. Select the result cell and drag its AutoFill Handle down to apply the formula to other cells.

=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))),LEN(B5)))

Note: In the formulas, you can change the cell reference to the text string and the delimiter according to your needs.

Example #6 Split cells by capital letter

This section introduces a user-defined function to split words in cells by capital letter.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, then copy the below VBA code into the Code window.

VBA code: Split cells by capital letter in Excel

Function GetName(s As String, Num As Long) As String
'Updated by Extendoffice 20211116
  With CreateObject("VBSCript.RegExp")
    .Global = True
    .Pattern = "[A-Z][a-z]+|[A-Z]"
    GetName = .Execute(s).Item(Num - 1)
  End With
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a cell (D5 in this case) to output the word to the left of the first capital letter, enter the below formula into it and press the Enter key. Select this result cell and drag its AutoFill Handle right then down to get the other words.

=IFERROR(GetName($B5,COLUMNS($D:D)),"")

Note: In the code, $B5 is the cell you will split, $D:D is the column of the result cell. Please change them according to your own data.

Example #7 Split names in cells

Supposing you have a worksheet containing a column of full names, and want to split the full name into separate columns such as split the first and last name from the full name, or split the first, middle or last name from the full name. This section lists detailed steps to help you solve these problems.

Split full name into first and last name

As shown in the screenshot below, the first name, middle name and the last name are separated by a single space, to split only the first name and last name from a full name and put them in different columns, you can apply one of the following methods.

1) Split full name into first and last name with formulas

You need to apply two formulas separately to split a full name into the first name and last name.

Split the first name from a full name

You can apply a formula based on the LEFT and the SEARCH functions to split the first name from a full name.

Generic formula

=LEFT(cell, SEARCH(" ", cell) - 1)

1. Select a cell to output the first name.

2. Copy or enter the below formula into it and press the Enter key. Select this result cell and drag its AutoFill Handle down to split the first names from other full names.

=LEFT(B5, SEARCH(" ", B5) - 1)

Now you have split all first names from the full names in specified range of cells, go ahead to apply the below formula to split the last names.

Split the last name from a full name

Generic formula

=RIGHT(cell, LEN(cell) - SEARCH("^", SUBSTITUTE(cell," ", "^", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))

1. Select a cell next to the first name cell.

2. Copy or enter the below formula into it and press the Enter key. Select this result cell and drag its AutoFill Handle down to get the last names from other full names.

=RIGHT(B5, LEN(B5) - SEARCH("^", SUBSTITUTE(B5," ", "^", LEN(B5) - LEN(SUBSTITUTE(B5, " ", "")))))

Note: In the formulas, B5 is the full name cell you want to split. You can change it as you need.

2) Split full name into first and last name with an amazing tool

Formula is hard to remember for many Excel users. Here, we recommend the Split Names feature of Kutools for Excel. With this feature, you can easily split full name into first and last name easily with only several clicks.

1. Select the range of full name cells, click Kutools > Merge & Split > Split Names.

2. In the Split Names dialog box, only check the First name and the Last name box in the Split types section, and then click OK.

Tips: The selected range is displayed in the Range to split box, you can change it as needed.

3. Then another Split Names dialog box pops up, select a destination cell and click OK.

Then the full name in the selected cells will be split into first name and last name in bulk as shown in the screenshot below.

Split full name into first, middle and last name

As shown in the screenshot below, there is a full name column with first name, middle name and last name separated by a single space. To split the full name into first name, middle name and last name and put them in different columns, the below methods can help you.

1) Split full name into first, middle and last name with Text to Columns

The built-in feature – Text to Columns can help you easily split full name into first name, middle name and last name in Excel.

You can follow the steps mentioned above to apply the Text to Columns feature.

Note: In the Step 2 of 3 wizard, only check the Space box.

2) Split full name into first, middle and last name with formulas

You can also apply formulas to split a full name into first name, middle name and last name in Excel.

Generic formulas used to split full name

Split the first name

=LEFT(cell,SEARCH(" ", cell)-1)

Split the middle name

=MID(cell, SEARCH(" ", cell) + 1, SEARCH(" ", cell, SEARCH(" ", cell)+1) - SEARCH(" ", cell)-1)

Split the last name

=RIGHT(cell,LEN(cell) - SEARCH(" ",cell, SEARCH(" ",cell,1)+1))

Then apply the formulas in different cells to get the first name, middle name and last name.

1. In cell D5, enter the below formula and press the Enter key. Select this result cell and drag its AutoFill Handle down to get the first name of other full names.

=LEFT(B5,SEARCH(" ", B5)-1)

2. Enter the below formula in cell E5 and press the Enter key to get the first middle name. Select the result cell and drag its AutoFill Handle down to get the other middle names.

=MID(B5, SEARCH(" ", B5) + 1, SEARCH(" ", B5, SEARCH(" ", B5)+1) - SEARCH(" ", B5)-1)

3. To get the last names, enter the below formula into cell F5 and press Enter, and then select the result cell and drag its AutoFill Handle over the cells you need.

=RIGHT(B5,LEN(B5) - SEARCH(" ",B5, SEARCH(" ",B5,1)+1))

3) Split full name into first, middle and last name with an amazing tool

Here is an introduction to Kutools for Excel’s Split Names feature, which allows you to split a full name into first name, middle name and last name at once with just a few clicks.

1. Select the full name cells where you want to split, and then click Kutools > Merge & Split > Split Names.

2. In the Split Names dialog box, you need to configure as follows.

3. In the next popping up Split Names dialog box, select a destination cell to output the separated texts, and then click OK. See the below demo.

Example #8 Split text and numbers in cells

Supposing there is a list of text strings with mixed texts and numbers as shown in the screenshot below, to separate the text and numbers and put them in different columns, we provide four methods for you.

Split text and numbers with formulas

With the following formulas, you can split the text and numbers from one cell into two separated cells.

Generic formulas

Get text from cell

=LEFT(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))-1)

Get numbers from cell

=RIGHT(cell,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))+1)

Then apply these two formulas in different cells to get the separate text and numbers.

1. Select a blank cell to place the text of the first text string in the list, copy or enter the formula below and press the Enter key. Select the result cell and drag its AutoFill Handle down to get the text of other text strings in the list.

=LEFT(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))-1)

2. Select a cell (E5) next to the first result cell (D5), copy or enter the formula below and press Enter. Select the result cell and drag its AutoFill Handle down to get the numbers of other text strings in the list.

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

Split text and numbers with Flash Fill (2013 and later versions)

If you are using Excel 2013 or the later versions, you can apply the Flash Fill built-in to split text and numbers from one cell to two columns.

Note: To make the Flash Fill feature work, you need to locate the result columns next to the original text string column. For example, if the original text strings locate in column B, the separated text and numbers should place in column C and D. See screenshot:

1. Manually type the text of the first text string cell (D5) in cell C5.

2. Keep typing the text of the second text string cell (D6) in cell C6.

3. Activate the cell C7, click Data > Flash Fill.

Then the texts of other text strings are filled in the cells automatically as shown in the screenshot below.

4. Repeat the step 1 to 3 to get the numbers in column D.

Notes:

Split text and numbers with User-defined Function

If the text strings in a list are not regular, the above two methods may return wrong results as shown in the screenshot below.

Here, we introduce a user-defined function to help you split text and numbers from a cell into two columns no matter where the numbers are located in the text string. See screenshot:

1. Press the Alt + F11 keys.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA into the Code window.

VBA code: Split text and numbers from a cell into two columns

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Update by Extendoffice 20211105
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a cell to output the text of the first text string, enter the below formula and press the Enter key. Select this result cell and drag its AutoFill Handle down to get the text of other text strings in the same list.

=SplitText(B5,FALSE)

5. Select a cell next to the first text result cell to output the numbers, enter the below formula and press the Enter key. Select this result cell and drag its AutoFill Handle down to get the numbers of other text strings.

=SplitText(B5,TRUE)

Split text and numbers with an amazing tool

Here, we recommend the easiest way to split text and numbers at once from a cell into two columns. Applying the Split Cells feature of Kutools for Excel helps you to handle this problem with only a few clicks.

1. Select the text string cells where you want to split the text and numbers into two columns.

2. Click Kutools > Merge & Split > Split Cells.

3. In the Split Cells dialog box, you need to do the following settings.

3.1) The selected range is listed in the Split range box, and you can click the button to select a new range as needed;

3.2) In the Type section, choose Split to Rows or Split to Columns;

3.3) In the Split by section, select the Text and number radio button;

3.4) Click the OK button.

4. In the next Split Cells dialog box, select a cell to output the separated text and numbers, and then click the OK button.

Then you can see the text and numbers in selected cells are split into two columns at once as shown in the screenshot below.

2. Split number cells

This part describes how to split number cells in two situations: splitting a number with more than one digits into individual digits and splitting number at decimal to separate the integer and decimal portions.

Example #1: Split cells by digits

If you want to split a number with more than one digits into individual digit in different columns, try one of the below methods.

Split a number in a cell into individual digits with formula

The below formula can help to split a number into individual digits and put them in different columns.

Generic formula

=MID($A1, COLUMNS($A$1:A$1), 1)

Argument

1. Select a blank cell to output the first digit, enter the below formula and press the Enter key.

=MID($B3, COLUMNS($B$3:B$3), 1)

2. Select this result cell and drag its AutoFill Handle right to the cells to get other digits. Keep all the result cells selected, and then drag the AutoFill Handle down to get the individual digits of other numbers.

Note: In this formula, B3 is the cell containing the number to split into individual digits, and you can change it as needed.

Split a number in a cell into individual digits with VBA

The VBA code below can also help to split a number in a cell into individual digits in Excel. You can do as follows.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the VBA below into the Code window.

VBA code: split number into individual digits in Excel

Sub SplitNumberIntoDigits()
'Updateby Extendoffice 2021118
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Kutools for Excel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
 xInt = InputRng.Row

Application.ScreenUpdating = False

For Each Rng In InputRng
    xValue = Rng.Value
    xRow = (Rng.Row - xInt) + 1
    For I = 1 To VBA.Len(xValue)
        OutRng.Cells(xRow, I).Value = VBA.Mid(xValue, I, 1)
    Next
Next
Application.ScreenUpdating = True
End Sub

3. Press the F5 key to run the code, then a Kutools for Excel dialog box pops up, you need to select the range of number cells you will split and then click the OK button.

4. The second Kutools for Excel dialog box pops up, you need to select a cell to output the individual digits, and then click OK.

Note: This code can split a word into individual letters as well.

Then numbers in selected cells are split into individual digits and place in different columns.

Easily split a number into individual digits with an amazing tool

Kutools for Excel’s Split Cells feature is a handy tool to help you easily split number into individual digits in Excel.

1. After installing Kutools for Excel, select the range of number cells you will split, click Kutools > Merge & Split > Split Cells.

2. In the Split Cells dialog box, do the following settings.

2.1) In the Split range section, you can see the range you selected displayed in the text box. You can click the button to change the range as you need;

2.2) In the Type section, choose Split to Rows or Split to Columns according to your needs;

2.3) In the Split by section, select the Specify width radio button, enter the number 1 into the text box;

2.4) Click the OK button.

3. In the opening Split Cells dialog box, select a blank cell to output the digits, and then click the OK button.

Then numbers in selected cells are split into individual digits at once.

Example #2 Split number at decimal

This section discusses a few methods to split a number into integer and decimal portions in Excel.

Split number at decimal with formulas

The TRUNC function can be applied to split a number at decimal in Excel. You can do as follows.

Generic formulas

Get the integer portion

=TRUNC(A1)

Get the decimal portion

=A1-TRUNC(A1)

Argument

Now you can apply these two formulas to split numbers in a specified range of cells at decimal in Excel.

1. Select a cell to place the integer part of the first number cell, enter the formula below and press the Enter key. Select the result cell and drag its AutoFill Handle down to get the integers of other number cells.

=TRUNC(B5)

2. Select a cell next to the first result cell to place the decimal part, enter the formula below and press the Enter key. Select this result cell and drag its AutoFill Handle down to get the decimals of other number cells.

=B5-TRUNC(B5)

Split number at decimal with Text to columns

You can apply the Text to Columns feature to split number at decimal in Excel.

1. Select the range of number cells you will split at decimal, and then click Data > Text to Columns.

2. In the Convert Text to Columns Wizard – Step 1 of 3 dialog box, select the Delimited radio button and click the Next button.

3. In the Step 2 of 3 dialog box, only check the Other checkbox, enter a dot into the textbox, and then click the Next button.

4. In the Step 3 of 3 dialog box, click the button to select a destination cell to output the split integers and decimals, and finally click the Finish button. See screenshot:

Then you can see that the integer and decimal parts are split from selected cells as shown in the screenshot below. However, the decimal part loses its negative sign which might be necessary for the user.

Split number at decimal with Flash Fill (2013 and later versions)

In the example above we introduced the use of the Flash Fill function to split text and numbers in cells, and here we will introduce the same method to split number at decimal.

1. Enter a couple of examples. In this case, we enter the integer part of B5 in C5, enter the integer part of B6 in C6. See screenshot:

Note: For negative numbers, don’t forget to enter the minus sign together.

3. Select the cells including the couple of examples in the column you want filled in, go to click Data > Flash Fill.

Then integers are extracted from the specified number cells as shown in the screenshot below.

4. Repeat the steps above to split decimals from the same number cells.

3. Split date cells

Another frequently encountered situation is to split date cells. If you need to split date cells into separate day, month and year, or split date cells into separate date and time, use the following solution depending on your needs.

Example #1 Split date cells into separate day, month and year

Assuming that you have a list of dates in range B5:B9, and want to split each date value into three separate columns for day, month and year. Here are three methods to help you achieve the result of splitting dates.

Split date cells into day, month and year with formulas

You can apply three formulas based on the DATE function, MONTH function and YEAR function to split a date into separate day, month and year in Excel.

Generic formulas

=DATE(A1)

=MONTH(A1)

=YEAR(A1)

Argument

1. Create three columns to place the separate day, month and year.

2. Select the first cell in the Day column, enter the formula below and press the Enter key to get the day of the first date cell (B5). Select this result cell and drag its AutoFill Handle down to get the days of other date cells.

=DAY(B5)

3. Do the same operation as step 1 to apply the following formulas in the Month and Year column to get the separate month and year from the date cells.

Get the month of a date

=MONTH(B5)

Get the year of a date

=YEAR(B5)

Split date cells into day, month and year with Text to Columns

You can follow the steps above to apply the Text to Columns feature to split date cells into separate day, month and year in Excel.

Note: In the Step 2 of 3 dialog box, only check the Other box, and type a / symbol in the text box.

Split date cells into day, month and year with an amazing tool

The below demo demonstrates how to split date into separate month, day and year with the Split Cells feature of Kutools for Excel.

This feature helps you to achieve the result of splitting dates in bulk with only several clicks.

Click to know more about this feature.

Example #2 Split date and time in a cell

Supposing there is a date time list in B5:B9, and you want to split the dates and times into separate columns, this section will demonstrate different ways to help you get it done.

Split date and time in a cell with formula

You can apply the following formulas to split date and time into different columns in Excel.

1. Prepare two columns to place the dates and times.

2. You need to set the date column cells to Date format, and set the time column cells to Time format.

3. Select the first cell in the Date column, enter the formula below and press the Enter key to get date of B5. Select this result cell and drag its AutoFill Handle down to get the other dates.

=INT(B5)

4. Apply the following formula in the time column to get the times in B5:B9.

=B5-D5

Split date and time in a cell with Flash Fill (2013 and later versions)

If you are using the Excel 2013 and the later versions, you can apply the built-in Flash Fill feature to split date and time into different columns.

1. Create the Date and Time columns and enter a couple of examples you want as output. In the Date column, we enter the date of B5 in C5, and enter the date of B6 in C6. In the Time column, we enter the time of B5 in D5 and enter the time of B6 in D6. See screenshot:

2. Select the Date column (including the couple of examples) you want filled in, go to click Data > Flash Fill.

3. Select the Time column (including the couple of examples) you want filled in, and then enable the Flash Fill feature as well. Then dates and times in B5:B9 are split into separate columns as shown in the demo below.

Now, you have learned how to split cells in Excel with different methods in different situations. You will be able to make the best decision to determine which method you choose comes down to your specific scenario.