String to Decimial with Split function

Question

In VBA I wrote this

Function SPLITTER(data As String, delimiter As String) As String()
    SPLITTER = Split(data, delimiter)
End Function

Which works great for me

However, SPLITTER returns strings but I will exclusively use it for numbers. I now that I return it As String () but using any other type didnt work for me. I guess it interferes with the Split function

I use that function to automize things to converting the value of the cells to numbers manually does not work for me. Is there a way to let the function return the values as decimal?

Sorry if I may be incorrect in my terminology but I'm new to VBA.

score 3 · Answer 1 · edited Jul 06 '21 at 06:37

3

You could return numbers without changing the function by adding VALUE to the formula.

=VALUE(TRANSPOSE(SPLITTER(D5,";")))

edited Jul 06 '21 at 06:37

Pᴇʜ

56,719
10
49
73

answered Jul 06 '21 at 06:34

norie

9,609
2
11
18

Pᴇʜ · Accepted Answer · 2021-07-06T09:10:37.307

Either you use the VALUE formula as @norie suggested or you need to change your function so it converts the strings you get form Split into Double values.

Note that this cannot be done for the whole array at once and you have to convert each value. This might be slightly slower (on huge data or extensive use of the function) than using the VALUE formula. For small data you won't see a difference.

Option Explicit

Public Function SPLITTER(ByVal Data As String, ByVal Delimiter As String) As Variant()
    ' split strings
    Dim SplittedStrings() As String
    SplittedStrings = Split(Data, Delimiter)
    
    ' create variant array of the same size (variant so we can return errors)
    Dim Values() As Variant
    ReDim Values(LBound(SplittedStrings) To UBound(SplittedStrings)) As Variant
    
    ' convert each value into double
    Dim i As Long
    For i = LBound(Values) To UBound(Values)
        If IsNumeric(SplittedStrings(i)) Then
            ' return value as double
            Values(i) = CDbl(SplittedStrings(i))
        Else
            ' return #VALUE! error if a value is not numeric
            Values(i) = CVErr(xlErrValue)
        End If
    Next i
    
    SPLITTER = Values
End Function

Note that the return array is defined as As Variant() and not As Double() so in case any of the splitted strings is not numeric it can return a #VALUE! error for this one value and still output the others. If you don't do that the whole function fails and does output #VALUE! for all values even if only one cannot be converted.

Since you re-write the whole array anyway you could even bring it into the correct direction: Output as row or as column:

Option Explicit

Public Function SPLITTER(ByVal Data As String, ByVal Delimiter As String, Optional ByVal OutputAsRow As Boolean = False) As Variant()
    ' split strings
    Dim SplittedStrings() As String
    SplittedStrings = Split(Data, Delimiter)
    
    ' create variant array of the same size (variant so we can return errors)
    Dim Values() As Variant
    If OutputAsRow Then
        ' 2-dimensional array with 1 row and n columns
        ReDim Values(1 To 1, LBound(SplittedStrings) To UBound(SplittedStrings)) As Variant
    Else
        ' 2-dimensional array with n rows and 1 column
        ReDim Values(LBound(SplittedStrings) To UBound(SplittedStrings), 1 To 1) As Variant
    End If
    
    ' convert each value into double
    Dim i As Long
    For i = LBound(SplittedStrings) To UBound(SplittedStrings)  ' for each value in the input string string
        Dim RetVal As Variant
        If IsNumeric(SplittedStrings(i)) Then  ' check if it is a number
            ' return value as double
            RetVal = CDbl(SplittedStrings(i))
        Else
            ' return #VALUE! error if a value is not numeric
            RetVal = CVErr(xlErrValue)
        End If
        
        If OutputAsRow Then
            Values(1, i) = RetVal  ' fill columns
        Else
            Values(i, 1) = RetVal  ' fill rows
        End If
    Next i

    SPLITTER = Values
End Function

So =SPLITTER($A$1,";") or =SPLITTER($A$1,";",0) will output it as a column and =SPLITTER($A$1,";",1) as a row.

@Kuehlschrank If you have questions to this code let me know. — Pᴇʜ, Jul 06 '21 at 09:06

score 3 · Answer 3 · answered Jul 06 '21 at 07:15

3

It seems you have a spilled range of values, which indicates you are using Microsoft 365. I'd suggest to not even use UDF's if not needed (and keep an .xlsx file instead):

=FILTERXML("<t><s>"&SUBSTITUTE(A1,";","</s><s>")&"</s></t>","//s")

This will then "split" your string through valid xml/xpath expressions and excel will auto-recognize the numbers.

answered Jul 06 '21 at 07:15

JvdV

70,606
8
39
70

2

@Kuehlschrank It's beneficial that numeric node values returned by the `FILTERXML` function will be interpreted immediately as numbers. - Suggest to read JvdV 's nearly encyclopaedic post about [Extracting substrings via FilterXML](https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml/61837697#61837697) for further background. – T.M. Jul 06 '21 at 14:43
1

fyi Posted an extension to your valid solution restricting tokens to numeric elements only :-) – T.M. Jul 06 '21 at 20:25

score 2 · Answer 4 · answered Jul 06 '21 at 20:24

Extension on JvdV 's solution

Assuming that the cell content BV71 contains non-numeric tokens, you might even restrict all xml node contents to numeric elements only via XPath "//s[.*0=0]":

    =FILTERXML("<t><s>"&SUBSTITUTE(A8,";","</s><s>")&"</s></t>","//s[.*0=0]")

Explanation: The XPath expression to search all s nodes (at any hierarchy level) gets an additional condition in [] brackets. Multiplying the node content (symbolized by .) with zero (*0) and checking if the result equals a number (i.e. =0) allows to get only numbers, as this multiplication fails with strings.

Note that regional date settings with point delimiters may interprete tokens like 8.2 as date, which would need further conversions.

String to Decimial with Split function

4 Answers4