How can I convert a text containing multiple items separated by commas into a list?

Kairi

I have a text string like "X1,X2,X3,X4,X5" and I want to convert it into a list containing X1, X2, X3, X4, and X5. I tried using FINDITEM, but it only works for one item, and with FIND, I can only get the position of the first comma. How can I achieve this?

Thanks

KirillKuznetsov

Hi @Kairi

There are different approaches you can use here.

It depends on whether the list you are trying to search exists in your model already or not.

If the list EXISTS:

You can crosscheck all items' names against the string using the same FIND() function but instead of comma you will be searching for names matches.

• Of course it means that there might be some situations when something is checked twice such as when X11 in the string will be counted against X1 because the name begins the same. But then you will have to make additional checks such as taking comma before and after as delimiters…

If the list does NOT EXIST:

Then you are actually right about using both of these formulas. But you have to make it sequential to check all commas that may appear in the text string. So you will have to iteratively calculate similarly but with different starting point of the string.

If you know the maximum number of possible items in this list:

Create line items set per each comma. You may have the following line items:

• Initial string
• Item 1 text
• Item 1
• Comma 1 position
• Item 2 text
• item 2
• Comma 2 position
• Item 3 text
• Item 3
• Comma n position
• …

For comma position, you need to use the third (optional) argument and link it to the position of the previous comma:

You can see this list of line items can be pretty long. But it suits well if you have a known number of maximum possible items in this list.

If you DON'T know the maximum number of possible items in this list:

This one is a little trickier to set, but it will be easier to maintain later.

If you don't know the number of commas you may have, then you will need to create a number sequence from 1 to N, where N is a number of commas + 1. But you will have to refer to the previous element of the sequence so you have to use the built-in TIME dimension because the PREVIOUS() formula can be used only there. You will have to apply it as a dimension (Days) to your calculation module. Using a similar to the previous approach the line items will be the following:

• Initial string ( TEXT, no time applied)
• First Item? (BOOLEAN, TIme applied)
• Previous comma position (LIST, Time applied)
• Comma n position: FIND(",",Initial string, Previous comma position)
• Item n text
• Item n

Let me know if you need more details regarding the last option as it is kind of advanced.