Office_Drone
New Member
- Joined
- Jun 17, 2024
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
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.:
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!!![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
Here's some sample output (hope I did this correctly?)
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 |