Hello
I am planning to capture some structured text, as MS Form has no tabular input, I created a multiline text question.
And the text responses will be like:
3 dummy responses, and text are seperated by ; and line beak.
I plan to limit line breaks to 10 but number of response is not fixed(depend on users input).
| 10320103;Fluid Det;2 |
10321856;Electrode PO2;3
10310750;Syring 250ul;1
10320103;Fluid Det;2
10321856;Electrode PO2;3
10310750;Syring 250ul;1
10321856;Electrode PO2;3
10310750;Syring 250ul;1
10321856;Electrode PO2;3
10310750;Syring 250ul;1
10321856;Electrode PO2;3 |
10321856;Electrode PO2;3
10310750;Syring 250ul;1 |
I want to the final result to be like:
| ID | Name | No |
| 10320103 | Fluid Det | 2 |
| 10321856 | Electrode PO2 | 3 |
| 10321856 | Electrode PO2 | 3 |
| 10310750 | Syring 250ul | 1 |
| 10310750 | Syring 250ul | 1 |
| 10320103 | Fluid Det | 2 |
| 10321856 | Electrode PO2 | 3 |
| 10310750 | Syring 250ul | 1 |
| 10321856 | Electrode PO2 | 3 |
| 10310750 | Syring 250ul | 1 |
| 10321856 | Electrode PO2 | 3 |
| 10310750 | Syring 250ul | 1 |
| 10321856 | Electrode PO2 | 3 |
Environment:
-Response from MS Form linked Excel (in One Drive)
-Would like to process data using formulas in Excel Online
-End result table will then be handled by MS Flow
The problem I am facing are:
1. In a normal excel sheet, I can use text to column ctrl+J for the line break, text to column again with the ;
But I am planning to automate it (as is a part of a flow) are there any formula base alternative?
2. Aftern the first Text to column for line break I get:
| 10320103;Fluid Det;2 | | | | | | | | | |
| 10321856;Electrode PO2;3 | 10310750;Syring 250ul;1 | 10320103;Fluid Det;2 | 10321856;Electrode PO2;3 | 10310750;Syring 250ul;1 | 10321856;Electrode PO2;3 | 10310750;Syring 250ul;1 | 10321856;Electrode PO2;3 | 10310750;Syring 250ul;1 | 10321856;Electrode PO2;3 |
| 10321856;Electrode PO2;3 | 10310750;Syring 250ul;1 | | | | | | | | |
Is there a way to make it into a single column by formula?
