Helpful formatting/delimeters for a multi-use text box?

[Office_Drone]

Hi there!

I need to compile the results of an online workplace survey; the results export to a CSV.

Unfortunately, the way this thing was designed there are multiple questions that prompt for a comment to be added to a textbox - but there's just one textbox to capture all the potential comments. Users specified which question they were adding the comment to by adding the question number, but the formatting is all over the place!

E.g.:

• 5. Problem X occurred 7. Deadline revised on 2024/04/16
• 12 & 13: John Doe
• 3) Situation Y occurred on May 12

I don't think I can unscramble these eggs, so I'm resigned to a lot of manual work this go around. But the next time this goes out, I can get instructions added to at least prompt users to use a common format. Any ideas on which delimeters I should specify they use? And the best formula to leverage them to separate the output (some kind of textsplit, I'm thinking?)

Thanks in advance! Future me is grateful to you!!

Here's some sample output (hope I did this correctly?)

sample workbook.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Review ID	Staff	Review Date	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11	Q12	Q13	Q14	Q15	Notes
2	21114	Mouse, Mickey	2024-06-07	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A		N/A	N/A	Yes	N/A	14. Bob Smith
3	21097	Mouse, Minnie	2024-06-07	Yes	Yes	No	No	Yes	N/A	Yes	No	No	N/A	No.	Yes	N/A	N/A	N/A	3. File delayed by client. 4. File reopened. 9. Classification changed.
4	21082	Duck, Donald	2024-06-06	Yes	Yes	Yes	Yes	N/A	Yes	Yes	Yes	Yes	N/A	No.	No	Yes	N/A	N/A	12. Marsha 13. Marsha
5	21049	Potter, Harry	2024-06-04	Yes	Yes	Yes	No	Yes	No	Yes	Yes	Yes	N/A	No.	Yes	No	N/A	No	4) File extended May 8 and May 23. 5) Client added additional work order. 6) Discount applied.
6	21045	Granger, Hermione	2024-06-04	Yes	Yes	Yes	Yes	N/A	No	Yes	Yes	Yes	N/A	No.	No	Yes	N/A	No	6) Discount applied. 12) & 13) Goofy emailed 3 times.
7	21039	Wayne, Bruce	2024-06-04	Yes	Yes	No	Yes	N/A	Yes	Yes	Yes	Yes	No	No.	No	Yes	Yes	N/A	2) Late submission 12) Deadline missed - Dumbledore & Snape 14. Lucifer
8	21032	Wilde, Oscar	2024-06-04	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A		N/A	N/A	Yes	N/A	14. Tom & Jerry
9	21025	Bronte, Anne	2024-06-03	Yes	No	Yes	Yes	N/A	N/A	Yes	No	No	N/A	No.	Yes	No	N/A	N/A	2) Project started May 17
10	20921	Hepburn, Katharine	2024-06-05	Yes	Yes	Yes	Yes	N/A	No	Yes	Yes	Yes	N/A	No.	No	Yes	Yes	N/A	12. Bill & Ted 13. Bill & Ted 14. Jan
review_entries_2024-06-01_-_202

 

[FormR]

Hi, welcome to the forum, if your sample data is accurate then it looks like the notes have been separated using a line feed character. If that's the case you can split them like this.

Book5
	S	T	U	V
1	Notes
2	14. Bob Smith	14. Bob Smith
3	3. File delayed by client. 4. File reopened. 9. Classification changed.	3. File delayed by client.	4. File reopened.	9. Classification changed.
4	12. Marsha 13. Marsha	12. Marsha	13. Marsha
5	4) File extended May 8 and May 23. 5) Client added additional work order. 6) Discount applied.	4) File extended May 8 and May 23.	5) Client added additional work order.	6) Discount applied.
6	6) Discount applied. 12) & 13) Goofy emailed 3 times.	6) Discount applied.	12) & 13) Goofy emailed 3 times.
7	2) Late submission 12) Deadline missed - Dumbledore & Snape 14. Lucifer	2) Late submission	12) Deadline missed - Dumbledore & Snape	14. Lucifer
8	14. Tom & Jerry	14. Tom & Jerry
9	2) Project started May 17	2) Project started May 17
10	12. Bill & Ted 13. Bill & Ted 14. Jan	12. Bill & Ted	13. Bill & Ted	14. Jan
Sheet1

Cell Formulas
Range	Formula
T2,T8:T9,T10:V10,T7:V7,T6:U6,T5:V5,T4:U4,T3:V3	T2	=TEXTSPLIT(S2,CHAR(10))

 

[Office_Drone]

Thanks for the reply! Since different users only need to add comments for certain questions, that means that the answers in the columns won't line up. That's why in the output above, column T has answers to multiple questions. Is there anything I can do so that all the comments for question 1 are in the same column, question 2 in the next column, etc.? And if a user doesn't add comments for certain questions, the fields in those columns will be blank?

 

[FormR]

Hi, I don't have a solution for the "12) & 13)" scenario and in those cases the comment will appear in the column of first listed number. But here is something you could try in T2 copied down.

=LET(ts,TEXTSPLIT(S2,CHAR(10)),S,SEQUENCE(,15),IFERROR(INDEX(ts,MATCH(S,BYCOL(ts,LAMBDA(bc,LEFT(bc,MATCH(FALSE,ISNUMBER(MID(bc,{1,2,3},1)+1),0)-1)+0)),0)),""))

 

[Office_Drone]

Thank you! That works great, and is very forgiving in terms of formatting. I will use this!