Split function is not a valid function


After seeing the split function tutorial, I decicde that was one I needed.  Unfortunately, I when using the function I get the error message the the function is not valid.

I am using Office 365

how do I ge tthis function to be available?




The SPLIT function belongs to VBA. It isn't part of Excel because it returns an array. Spreadsheets show elements of arrays in different cells. Therefore the result of the SPLIT function can't be shown in a single cell which, Excel being unable to display the result, would render the function itself useless.

VBA, on the other hand, could be used to write the result of a SPLIT function in a range of cells. Unfortunately, it wouldn't be practical to call that function using a command in any one cell. Rather, it would be a sub routine which the user calls by pressing a button or keyboard shortcut.

However, you might use an array embedded within a formula which extracts just a single value from it and then uses that value to produce a result. To demonstrate, I created a UDF (User Defined Function - store in a standard code module):

Function TestResult(Cell As Range) As Variant()

    Dim Variable As Single

    Variable = Val(Cell.Value)
    TestResult = Array(1 * Variable, 2 * Variable, 3 * Variable)
End Function

In the worksheet, I called this function with (enter in any cell except A1)


The result of this function will change with the value in A1.

Now, SPLIT returns an array of string type while the above example works with a numeric array. Comparison is difficult even though the principle is the same. On aggregate the above seems convoluted. There should be easier ways to get the same result. That principle may be transferrable, too. Therefore, before suggesting any "solution" I would recommend to look at the desired result.

How do you wish to use the SPLIT function in your worksheet? That question, obviously, is one that needs to be posed in another thread. The upshot of that is likely to be either a worksheet function that avoids using the SPLIT function or a UDF which returns a single string or number.



Variatus, thanks for the detailed response.  To answer your question - how do I wish to use the SPLIT function...  Many times, in my business I export data from various sources and import to Excel. Many times, due to headers and footer, as well as overlapping column titles, it is impossible to use the import wizard and delimit the data into respective columns, thus a single column may have many cells with multiple pieces of data in the cell to be parsed out to separate columns.  The SPLIT function as shown in the tutorial will be a valuable tool to help formatting the export.

All that said, I did find the solution to my problem.  As you noted the SPLIT function is a VBA function and not an Excel function and that is what confused me - as I could not find it in Excel. I eventually realized after a few google searches, that I need to create the macro (VBA) and save it as an .xlam in and add it to Excel for the function or macro to work.



DaveSp (rep: 2) May 31, '19 at 8:06 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login