One of our customers approached me with the following question:
we made a survey with another tool and stored all the answers in one “long text field”. Is it possible to split the content of this cell to get all the concrete answers? So here is an example of how this looked like: The questions and all the answers were stored in one cell.
The goal is to split the content of this cell and to get only the answers to new columns.
How to solve this in three steps
Step 1
Create new text columns and name them like Q1 for question1, Q2 for question2 etc. Paste the concrete questions into these columns. Use Copy-and-paste to fill all rows with these questions. This makes it much easier to understand the formula I will use in step 2.
Step 2
Now create your first formula column and use the following formular:
trim(
mid(
{input},
find({Q1}, {input}, 1) + len({Q1}),
find({Q2}, {input}, 1) - find({Q1}, {input}, 1) - len({Q1})
)
)
Let me try to explain this formula from inside to the outside.
- mid(string, startPosition, count): Returns the specified number of characters from the specified start position from a string.
- Easy, right? I throw in the complete text, then I look for the end of question no. 1 and then I get the length of the answer by subtracting the positions of question 2 with question 1.
- trim(text): Removes spaces at the beginning and end of a string.
Imagine it like this:
Step 3
Create more formula columns and slighly adapt your formula by increasing the numbers of the questions.
trim(
mid(
{input},
find({Q2}, {input}, 1) + len({Q2}),
find({Q3}, {input}, 1) - find({Q2}, {input}, 1) - len({Q2})
)
)
The result
Now you have all the answers in separate columns.
