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.
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.
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
'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:
|[A-Z].+?||This will catch all words and whitespaces starting with a Capital letter the ? sign means that this is a non-greedy regular expression|
|([A-Z].+?)||The () brackets will capture inside any pattern matching this regular expression|
|([A-Z].+?)[A-Z].+||The final regex this will capture only the first words and whitespaces starting with a Capital letter. Notice that the capture will end at the next Capital letter|
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:
Again let us break it down:
|[^\s]||This specifies a non-whitespace character|
|[^\s]+||This specifies at least 1 non-whitespace character|
|([^\s]+)||The () brackets will capture inside any pattern matching this regular expression – hence all sequences of non-whitespace characters|
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.
The delimiter on which the Split operation is to be done.
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):