The easiest way on how to split Cells in Excel or split Columns in Excel, is to select the column you want to split. Next go to the Data ribbon and hover to the Data Tools group. Next Select Text to Columns and proceed according to the instructions.

The above works for simple splits on delimiters such as Commas, Semicolons, Tabs etc. However, for Non-Standard patterns such as Capital letters, specific word you might need to revert to more elaborate solutions such using VBA String functions or the VBA Regex. Read on to learn more.

CONTENTS

SPLITTING CELLS ON DELIMITER

SPLITTING CELLS ON PATTERNS

Splitting Cells using Text to Columns

A Delimiter is a sequence of 1 or more characters to separate columns within a Text String. An example of a Delimiter is the Comma in the following Text String Columns1,Column2 which separates the String Column1 from Column2. Popular Delimiters often used are: Commas (,), Semicolons (;), Dots (.), Tabs (\t), Spaces (\s). A Delimiter can be just as well any Sequence of Characters.

How to Split Cells in Excel using Text to Columns

The most obvious choice when wanting to Split Cells in Excel is to use the DATA Ribbon Text to Columns feature.

Select the Column

Select the Column with Cells you want to Split in Excel:

Select first column and proceed to Text to Columns

Select the entire first column where all your data should be located. Next click on the Text to Columns button in the DATA ribbon tab:

Proceed according to Wizard instructions

This is the hard part. Text to Columns need additional information on the delimiter and format of your columns.

Delimited or Fixed width?

Delimiters are any specific Sequence of Characters (like a comma or semicolon). Fixed Width means that each column in the Cell is separated by a Fixed Width of Whitespace Characters. In this case we select Delimited. Next click Next to proceed.

Select delimiter

Assuming your columns are separated with a specific Delimiter you need to provide this delimiter in the Wizard. Look at the Data preview to make sure your columns will be separated correctly. When finished proceed with Next

Format your columns (optional)

The last step is to format your columns if needed. If your columns represent Dates or you want to pull a column containing numbers/dates as text instead – be sure to format it appropriately. Usually, however, you are fine with hitting Finish:

The Resulting Split Columns

If you have proceeded according to the steps above you should have a neatly formatted spreadsheet like the one below.

Splitting Cells using Formulas

Another way of how to Split Cells in Excel is using the LEFT, RIGHT and LEN functions. See examples below:

Splitting against a Delimiter:

'Cell A1
Hello;There

The formula:

'To get "Hello"
=LEFT(A1;FIND(";")-1)

'To get "There"
=RIGHT(A1;LEN(A1)-FIND(";"))

Split Cells on Patterns

Sometimes instead of Delimiters you want to Split your Excel Cells on Patterns that are dynamic and may be different for each cell in a certain column. Fortunately Excel supports Regular Expressions, which allow you to Define Patterns on which your Cells are to be Split.

Let us first introduce my often used GetRegex UDF function:

'str - the original string. reg - the Regex pattern. index - the index of the capture if more than 1.
Public Function GetRegex(str As String, reg As String, Optional index As Integer) As String
    On Error Resume Next
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = reg
    regex.Global = True
    If index < 0 Then index = 0
    If regex.test(str) Then
        Set matches = regex.Execute(str)
        GetRegex = matches(index).SubMatches(0)
        Exit Function
    End If
    GetRegex = ""
End Function

To install it – open your DEVELOPER Excel Tab, click Visual Basic and add the code above to any new VBA Module.

If you are not familiar with Regular Expressions read the VBA Regex Tutorial.

What does this UDF function do? It extracts any text that matches a certain pattern. Let’s see it in action:

Example 1: Splitting Cells on Capital Letters

Let us take a common example where we have 1 Column of Cells that have 2 merged Columns inside. We want to split them on the second capital letter:

Now splitting this on the second capital letter using the FIND, LEFT, RIGHT and LEN functions will be a nightmare.

Let’s decipher the regular expression now:

Now for the second column:

Great right? Now just drag the formula across the rows – and you are done!

Example 2: Splitting Cells on Whitespaces

A simple example – let us Split an Excel Cell on a Variable number of Whitespace characters. Let us say the Words in our String can have 1 or more Spaces in between.

The Formula for the above is:

=GetRegex(A1;"\s*?([^\s]+)\s*?";0)

Again let us break it down:

Split Excel Function

If you just need an Excel Split function and you can introduce the following UDF Function (copy to VBA Module):

Public Function SplitFunction(str As String, delimiter As String, index As Long) As String
    SplitFunction = Split(str, delimiter)(index)
End Function

It uses the VBA Split function which is available in VBA only.

Parameters

str
A String.

delimiter
The delimiter on which the Split operation is to be done.

index
The index of the substring resulting from the Split.

How to use the Excel Split Function

The Function will be available as an Excel Function:

The above Formula looks like this (return second substring from Split):

=SplitFunction(A1;";";1)