Convert String to Collection

[Oyster]

I have a formula that will convert a collection to a string, like so:

Mid(Concat(OptionsCollection, "," & Option & ":" & QTY), 3, 1000)

which converts my collection:

OptionsCollection

|     Option    |    QTY     |

|  7147637    |       1       |

|  84622741   |      2       |

To this string:
7147637:1,84622741:2

I plan to store this string in a single cell on a connected data source to recall later. I would like to take a string like that and somehow "load" it into OptionsCollection, basically reversing the conversion i just did, allowing me to store and retreive collection data from a single cell. Any help?

[timl]

Hi @Oyster

In the thread beneath, @CarlosFigueira provides an excellent example on how you can you recreate a collection from a concatenated string.

https://powerusers.microsoft.com/t5/General-Discussion/Combine-rows-in-collection-separated-by-comma...

Carlos uses the | and & symbols to separate the fields and rows. In your example, you would replace these references with the , and : symbols.  Hopefully, this will make sense but feel free to post back if you need any further clarification.

[Oyster]

Thanks for the speedy reply!

Using that link i came up with this:

ClearCollect(
    OptionsCollection,
    ForAll(
        Split("3052260:1", ","),
        {
            Option: Left(Result, Find(":", Result) - 1),
            QTY: Value(Mid(Result, Find(":", Result) + 1))
        }))

The string "3052260:1" is just what im using for a test, ill reference a cell later, but i am getting this error:
Incompatible type. The item you're trying to put into a collection has a type that's not compatible with the collection

[timl · Accepted solution]

Hi @Oyster

The likely cause of this problem is that the data types of the fields in in item that you're adding doesn't match the data types of the fields where you used OptionsCollection originally.

For example, if 'Option' were a numeric field in the original place where you used OptionsCollection, you could resolve this problem by converting option to a number like so:

ClearCollect(
    OptionsCollection,
    ForAll(
        Split("3052260:1", ","),
        {
            Option: Value(Left(Result, Find(":", Result) - 1)),
            QTY: Value(Mid(Result, Find(":", Result) + 1))
        }))

The code that you posted is syntactically correct because if you were to ClearCollect the data to a new collection (OptionsCollection2 for example), you should find that you don't receive the error.