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
#Excel split cells on comma code#
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.
0 kommentar(er)
