arraysvbaexcelrtd

Parse an array returned from RTD in Excel

I am using an RTD formula in an Excel worksheet to receive a list of items in cell A1:

=RTD("josh.rtd",,"Letters",,"Lower")

The returned list in cell A1 is in curly bracket and delimited by semi colons. Basically, it's a one dimensional array of rows and looks like this

{"a";"b";"c";"d"}

If I copy that result, and paste the values in A2, then copy A2, select four cells from a column in a spreadsheet, such as A3:A6, and type an "=" in the formula window, and paste the result,

={"a";"b";"c";"d"}

I can hold down ctrl+shift+enter and populate each of those cells with an element of the array like this:

a

b

c

d

My question is this: is there a formula I can use to parse that original RTD formula result? I want to populate a combo box control with the elements of that array. I would love to be able to do this either in a cell formula or via VBA.

Solution

  • This documentation suggests that RTD returns a string. If so, the following should be able to parse it, returning the result as an array:

    Function ParseArray(ArrayString As String) As Variant
    Dim s As String
    s = Replace(ArrayString, "{", "")
    s = Replace(s, "}", "")
    s = Replace(s, """", "")
    ParseArray = Split(s, ";")
End Function