One common issue that many people have is to How to Split Columns in Excel upon non common delimiters e.g. Commas ,, Semicolons ;, or Non-Standard patterns such asCapital letters, a specific word – patterns which are not supported by the Text to Columns feature in the DATA ribbon tab.
How to deal with this? Usually you would need to resort to a VBA macro or really loooooong formula using the FIND, LEFT, RIGHT and LEN functions. That’s not an effective way of tackling this issue. What is the alternative? A simply Regex UDF function.
Splitting Cells on Delimiter
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 Tab
A Delimiter can be 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.
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:
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)
GetRegex = ""
To install it – open your DEVELOPER Excel Tab, click Visual Basic and add the code above to any new VBA Module.
Its parameters are:
The Regular Expression including 1 Capture ().
The index of the capture e.g. if your Regex captures 3 matches then index=0 returns the first one and index=2 returns the 3rd one.
What does this UDF function do? If extracts any text via a Regular Expression. 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
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)
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