Double a list of values in a cell

Skyler Wities

Is there a way to work with a list of values in a single cell?

My situation is: a form user enters a submission, that populates a single line in a smartsheet. Each submission may have any number of samples, each which has a value associated it with it. Currently, the form user enters the values on separate lines in the form field, so they end up as a list of values in a single cell. This is the format we'd like to use, because it works well for copying the data to external programs.

My question: could I make a column that doubles the values of the first column?

For example if the user enters: 2 4 2 5 2

The second column puts out: 4 8 4 10 5

I have not found a simple way to do this. I've tried a little to use hidden helper columns, however I don't think that would work as it needs to work for any number of values per line, and for all lines in the sheet.

I appreciate any help anyone could share. Thanks!

L_123

It would be much easier to do the inverse of this. You can have your users enter the values in separate fields in the form, then use the join formula to parse the values together for exporting. Otherwise you will have to write a fairly long text parsing equation in order to develop this. To keep the text parsing in a single cell would probably exceed the length of smartsheets formula character limit, and you would have to account (read duplicate) the formula for however many potential values the user could enter in the field, if they exceed the number of values the formula would ignore all values past what you have predicted.

Skyler Wities

But then the user would have to copy and paste each unique value in a field, rather than pasting them all at once how they do now, which would take much more time for them.

L_123

Is it always 5 values or is the number of entries variable?

Skyler Wities

No, it's variable number of entries. Usually will be none actually, but often will be up to 20, and could potentially be up to 50 values

L_123

Here is an example of 2 values if the column name is "A"

=VALUE(LEFT(A1, FIND(" ", A1) - 1)) * 2 + " " + VALUE(MID(A1, FIND(" ", A1) + 1, FIND(" ", RIGHT(A1, LEN(A1) - FIND(" ", A1))) - 1)) * 2

The first value is simple enough

VALUE(LEFT(A1, FIND(" ", A1) - 1)) * 2

The second value is where it gets interesting.

VALUE(MID(A1, FIND(" ", A1) + 1, FIND(" ", RIGHT(A1, LEN(A1) - FIND(" ", A1))) - 1)) * 2

Now the length of this second value formula is going to about double for every value after it.

That is going to be ~18,000 characters in the formula just if there are 20 values submitted using this method.

The only other method I can think of is using substitute and replacing the space with your math, but then you are left with a string expression.

@Andrée Starå or @J. Craig Williams Might have another solution to this, but I don't think it is going to be straightforward.

Skyler Wities

Hmm okay. I appreciate all the help. I'm definitely using smartsheet in a way that it wasn't exactly designed for, so I understand a solution to this is complicated. I think the simplest solution for my purposes is to ask the form user to submit the doubled values.

L_123

If you are willing to have a helper column for each potential value I can help you with that, but you'll have any extra 50 columns in your sheet.