I have a column A with comma separated words. In subsequent columns, I replace the words with IDs using Vlookup. I am intending to fill in data with an output that looks like this a:4:{i:0;s:2:"41";i:1;s:2:"40";i:2;s:2:"43";i:3;s:3:"153";}

I need to concatenate the first number with i:0;s:2: Where 0 is the first index of the numbers and 2 is the number of characters in the first number. The second number 40 becomes i:1;s:2: while the third 143 becomes i:2;s:3: Take note of 3 because 143 has 3 characters.

41 40 143

I have made strides but making it dynamic is where I am having the challenge. So that the formula is dynamic when the text in A changes.

My formulae in D2 =ARRAYFORMULA(IF(A3:A="","",(LEN(A2:A)-LEN(SUBSTITUTE(A2:A,",","")) 1))) This counts the number of words in A. in E2 using flatten, trim, split, textjoin I replace the words with IDs.

In K to N Columns I have the individual numbers and P to S columns I have the number character count.

The expected output is in F2. But the formula is unfinished and probably there is a much easier way to do it. My formula is not dynamic meaning it still needs fixing to work with the other words dynamically without changing the formula time and again.

Link to spreadsheet