The final regex this will capture only the first words and whitespaces starting with a Capital letter. The () brackets will capture inside any pattern matching this regular expression This will catch all words and whitespaces starting with a Capital letter the ? sign means that this is a non-greedy regular expression Let’s decipher the regular expression now: Pattern Now splitting this on the second capital letter using the FIND, LEFT, RIGHT and LEN functions will be a nightmare. We want to split them on the second capital letter: Let us take a common example where we have 1 Column of Cells that have 2 merged Columns inside. Let’s see it in action:Įxample 1: Splitting Cells on Capital Letters What does this UDF function do? It extracts any text that matches a certain pattern. If you are not familiar with Regular Expressions read the VBA Regex Tutorial.
#Excel split cells into columns install
To install it – open your DEVELOPER Excel Tab, click Visual Basic and add the code above to any new VBA Module. Set regex = CreateObject("VBScript.RegExp") Public Function GetRegex(str As String, reg As String, Optional index As Integer) As String index - the index of the capture if more than 1. Let us first introduce my often used GetRegex UDF function: Fortunately Excel supports Regular Expressions, which allow you to Define Patterns on which your Cells are to be Split. 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.
#Excel split cells into columns how to
If you have proceeded according to the steps above you should have a neatly formatted spreadsheet like the one below.Īnother way of how to Split Cells in Excel is using the LEFT, RIGHT and LEN functions. Usually, however, you are fine with hitting Finish: If your columns represent Dates or you want to pull a column containing numbers/dates as text instead – be sure to format it appropriately.
The last step is to format your columns if needed. Look at the Data preview to make sure your columns will be separated correctly. Next click Next to proceed.Īssuming your columns are separated with a specific Delimiter you need to provide this delimiter in the Wizard. Fixed Width means that each column in the Cell is separated by a Fixed Width of Whitespace Characters. Delimited or Fixed width?ĭelimiters are any specific Sequence of Characters (like a comma or semicolon).
Text to Columns need additional information on the delimiter and format of your columns. Next click on the Text to Columns button in the DATA ribbon tab: Select the entire first column where all your data should be located. Select first column and proceed to Text to Columns Select the Column with Cells you want to Split in Excel: The most obvious choice when wanting to Split Cells in Excel is to use the DATA Ribbon Text to Columns feature. How to Split Cells in Excel using Text to Columns A Delimiter can be just as well any Sequence of Characters. Popular Delimiters often used are: Commas (,), Semicolons ( ), Dots (.), Tabs (\t), Spaces (\s). An example of a Delimiter is the Comma in the following Text String Columns1 ,Column2 which separates the String Column1 from Column2. SPLITTING CELLS ON PATTERNS Splitting Cells using Text to ColumnsĪ Delimiter is a sequence of 1 or more characters to separate columns within a Text String.