Excel tip - How to split text from one cell to multiple cells

1) Using the new TEXTSPLIT function

There’s a new function in Excel called the TEXTSPLIT function. It separates (or splits) text strings automatically into multiple cells (columns or rows), using a "delimiter". A delimiter can be a comma, a space, colon, or a combination different delimiters etc.

The function works same as Text to Columns features (link to Excel 2007), but in the form of a formula.

However, at this moment the TEXTSPLIT function is only available in Excel for the web and Microsoft 365. So, if you have access to either Excel for the web or MS 365, then you can use this function.

No worries if you do not have MS 365 or Excel for the web, you can still split strings (or texts) from one cell to multiple cells using a Macro.

Ok, now lets see how the TEXTSPLIT() function works.

Syntax

TEXTSPLIT (text, col_delimiter, row_delimiter, ignore_empty, match_mode, pad_with)

The function takes 6 parameters (or arguments). The first two arguments are required (or compulsory).

• text: The text that you want to split. It can be a cell value or a string. This is required.

• col_delimiter: A text or character that marks the point where to split the text across columns. This is required.

• row_delimiter: A text or character that marks the point where to split the text across rows. Oh yes, you can now split cell values or a string or texts down rows, easily.

• ignore_empty: This is to specify whether to ignore empty values or not. It takes a Boolean true or false. The default value is FALSE.

• match_mode: Searches a text for a delimiter match.

• pad_with: A value to use for padding.

Now, let’s work with it.

I am using Excel for the Web and I have signed in to it.

I have three columns in my Excel worksheet. The first column has Full Name, which has texts separated by a space. The formula to split the texts into multiple columns should be,

TEXTSPLIT(A2, " ")

See this image.

This is not just limited to splitting the text horizontally (in columns). Using the TEXTSPLIT() function you can split text vertically, that is down the rows. For example,

TEXTSPLIT(A2, , " ")

Here, I have ignored the 2nd argument and assigned space (within double quotes) to the 3rd argument. The result will be,

Related Tutorial... How to split cell values with "Carriage Returns" to Multiple Columns with VBA Split() function

2) Split text into multiple cells using VBA Macro

In case you do not have access to TEXTSPLIT() function, which I have explained above, you can use a simple macro (a program in VBA) to do the same. This will just automate the whole process.

Let us see how we can split texts (in cells) into multiple cells using a small piece of code, with the click of a button.

Option Explicit

Private Sub cmd_Click()
    splitText
End Sub

Sub splitText()

    Dim str() As String
    
    Dim rng, rngCell As Range
    Set rng = Selection    ' The range or the cells that you have selected.
    
    For Each rngCell In rng
        If Trim(rngCell) <> "" Then
            str = VBA.Split(rngCell, " ")    ' Change delimiter according to your requirement.
            rngCell.Resize(1, UBound(str) + 1).Offset(0, 1) = str
        End If
    Next rngCell
End Sub

The macro splits texts (in cells) that are selected. So, if I select all the cells in the 1st row, it will split all the texts in the next columns. Alternatively, you can select limited cells (or texts that you want to split), by holding the Ctrl key and clicking the mouse.

Here I am using VBA Split() function to split the texts. The function takes 4 arguments. However, I have defined only the first two, the text and a delimiter (space).

Split a text to multiple cells in Excel 2007 (using Text to Columns wizard)

In older versions like Excel 2007, you can use the Text to Columns wizard to split texts from a cell to multiple cells.

Just follow these steps.

1) Select the cells that you want to split, navigate to the Data tab from the top menu and click the Text to Columns option.

2) It will open the Convert Text to Columns Wizard. Choose the option Delimited and click the Next button.

3) Next, specify the delimiters. You can choose multiple options. Since the texts in my worksheet cells have a single space, I chose Space delimiter.

4) Finally, choose the destination cells (or range) where you want to split the texts.

   • Click on the "Destination" box

   • Select the range of cells

   • Click the "Finish" button.

Done!

← PreviousNext →