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?
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)
=SUM(TestResult(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.