Tag Archives: split columns

split cells in excel

How to Split Cells in Excel – Split Excel Columns

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

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.

CONTENTS

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
  • Spaces

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:

how to split cells in excel select column
Select Column

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:

Data ribbon: Text to Columns
Data ribbon: Text to Columns

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.

Text to Columns Wizard: Step 1
Text to Columns Wizard: Step 1

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

Text to Columns Wizard: Step 2
Text to Columns Wizard: Step 2

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:

Text to Columns Wizard: Step 3
Text to Columns Wizard: Step 3

The Resulting Split Columns

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

how to split cells in excel the result
Splitted Columns in Excel

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:

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

str
Any String.

reg
The Regular Expression including 1 Capture ().

index
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:

Split columns: Split the first column
Split first column

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:

Split columns on regular expressions
Splitting columns on regular expressions

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:

Pattern Description
[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:

Split columns: Split second column
Split 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.
split columns on pattern non whitespace
The Formula for the above is:

Again let us break it down:

Pattern Description
[^\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):

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:
excel split string function
The above Formula looks like this (return second substring from Split):