I recommend you download the example file for this post. Thankfully we can turn to a variety of Excel features. But if we have hundreds or thousands of cells, we need to find another way. If we have ten cells, it’s not a big problem we can do that manually by entering each value into a separate column.
The scenario we are trying to solve is we want to split an individual’s full name into the first name and last name components.
This post will look at solving this problem and learn how to split cells in Excel. In these circumstances, we often need to split a cell into its constituent parts. This often occurs when the data’s original intention is slightly different from how we intend to use it. Similarily, to split data into rows we need to combine Splitter2 with ROWS function.There are many circumstances where we receive information with multiple data points inside a single cell. To split data into multiple columns we need to combine Splitter2 with COLUMNS function. In the following example, I have used Splitter2 function to return the second item from the Text string ‘Snickers,Mars,Twix,Bounty,Galaxy’ =Splitter2("Snickers,Mars,Twix,Bounty,Galaxy",",",2) Splitter2 will accept 3 arguments ‘Data’, ‘Separator’, ‘Item’ and return the item from the specified position. In this function called Splitter2, we have introduced one more variable called ‘Item’ which can be used to refer the index number of the item to return. Splitter2 = Split(Data, Separator)(Item - 1) Another version of Splitter Function Function Splitter2(Data As String, Separator As String, Item As Integer) As String Those who are using Excel 2019, Excel 2016 or earlier versions of Excel which doesn’t support Dynamic Array Formulas can use another version of the same function. This spill behavior of Splitter function will be available to those whose have access to the new Dynamic Array Formulas (Office 365 Subscription). =Splitter("Aubrey Drake Graham"," ")Īnother example where Splitter function is used to split data separated by comma.īy combining it with TRANSPOSE function, Splitter function can be used to split data into multiple Rows. When ‘Aubrey Drake Graham’ is given into the Splitter function, it spills ‘Aubrey’, ‘Drake’ and ‘Graham’ into 3 different columns. In the following example, I have used this Splitter function for splitting a name into First, Middle and Last Name on the basis of the ‘Space’ character separating them.
Data refers to the text to split and Separator is the delimiter. This new function will accept two arguments ‘Data’ and ‘Separator’. End Function - Line 5Īfter creating this UDF called Splitter, when we type in the first few letters of the same, this function will appear in the list of suggestions. On Error GoTo 0 - Line 4ĭisables any enabled error handler in the current procedure. Here, Data is the variable which refers to the text to split and Separator is the variable for the delimiter. Split function is a VBA function that will split data on the basis of the specified delimiter. Splitter = Split(Data, Separator) - Line 3 In case of any error (run-time error for example) control goes to the next statement.
Splitter is the name of the string function which will accept two strings ‘Data’ and ‘Separator’ On Error Resume Next - Line 2
Function Splitter(Data As String, Separator As String) As String()Įnd Function Explanation of the code Function Splitter(Data As String, Separator As String) As String() - Line 1 To create a User Defined Function called Splitter, open the VBA Editor of Excel and type in the following code.