Sorting MCQ questions alphabetically

I have a text file with MCQ's. after importing it every line got a separate cell
The questions are not numbered and do not have any spaces between them.

Some have ABCD. some have ABCD E as answers. As you see some questions have multiple lines (sometimes even 4 lines).. same holds for answers. The only thing that I can think of is that the first Letter of the question is Always capital and is never starting with the followin 3 characters "A. " or "B. " , "C. " or "E. " .. etc

This is a bout 150000 lines of data, and what I'm trying to achieve is each question in a separate cell (possibly using concatenate function and all the cells sorted alphabetically

they look in a similar format:

Someone with history of shortness of breath you would
suspect
A. asthma.
B. emphysema.
C. typhoid.
D. cardiac insufficiency.

so that they would look like "Someone with history of shortness of breath you would suspect A. asthma. B. emphysema. C. typhoid. D. cardiac insufficiency." all in one cell

Thanks , i would really appreciate any help I could get in achieving this

With your data in A1:A?? put this in B1:

=IF(MID(A2,2,1)=".",A1&" "&A2,IF(EXACT(PROPER(LEFT(A2,2)),LEFT(A2,2)),"",A1&" "&A2))

and copy over and down. This will concatenate each successive string until it runs into the next question.

It works by adding spaces, which is good, but it does not concatenate all the cells into one line.
I've attached sample file with original data in A1:A, Your formula applied in B1:B. And sample of what I'm trying to achieve in C1:C

Thanks for helping, hopefully I can get some advice how to modify your original formula.

i'm not sure if my idea is good, but maybe we can then concatenate cells using if statement, till the moment we see a a blank cell now as produced by your formula above

Originally Posted by daffodil11

and copy over and down.

You only copied it into one column. Keep dragging the formula over. Each column will concatenate an additional step.

I've did as you recommenced it seems to work but somehow the previous line is duplicated and in the end the text is distorted as it repeats too many times

Cushing's syndrome?
A. Estradiol.
B. Testosterone.
C. Prednisolone.
D. Progesterone.
E. Diethylstilbestrol. .

is turned into one cell, but the text repeats too many times

Cushing's syndrome? A. Estradiol. A. Estradiol. B. Testosterone. A. Estradiol. B. Testosterone. B. Testosterone. C. Prednisolone. A. Estradiol. B. Testosterone. B. Testosterone. C. Prednisolone. B. Testosterone. C. Prednisolone. C. Prednisolone. D. Progesterone. A. Estradiol. B. Testosterone. B. Testosterone. C. Prednisolone. B. Testosterone. C. Prednisolone. C. Prednisolone. D. Progesterone. B. Testosterone. C. Prednisolone. C. Prednisolone. D. Progesterone. C. Prednisolone. D. Progesterone. D. Progesterone. E. Diethylstilbestrol. .

I see my mistake; the iteration is compounded the data from below. Let's go in a different direction.

This is a little more brute force than is probably necessary but I'm not exactly known for truncation or elegance.

=IF(OR(MID(A1,2,1)=".",NOT(EXACT(PROPER(LEFT(A1,2)),LEFT(A1,2)))),"",A1&
IF(MID(A2,2,1)=".",A2,IF(NOT(EXACT(PROPER(LEFT(A2,2)),LEFT(A2,2))),A2,""))&" "&
IF(MID(A3,2,1)=".",A3,IF(NOT(EXACT(PROPER(LEFT(A3,2)),LEFT(A3,2))),A3,""))&" "&
IF(MID(A4,2,1)=".",A4,IF(NOT(EXACT(PROPER(LEFT(A4,2)),LEFT(A4,2))),A4,""))&" "&
IF(MID(A5,2,1)=".",A5,IF(NOT(EXACT(PROPER(LEFT(A5,2)),LEFT(A5,2))),A5,""))&" "&
IF(MID(A6,2,1)=".",A6,IF(NOT(EXACT(PROPER(LEFT(A6,2)),LEFT(A6,2))),A6,""))&" "&
IF(MID(A7,2,1)=".",A7,IF(NOT(EXACT(PROPER(LEFT(A7,2)),LEFT(A7,2))),A7,""))&" "&
IF(MID(A8,2,1)=".",A8,IF(NOT(EXACT(PROPER(LEFT(A8,2)),LEFT(A8,2))),A8,""))&" "&
IF(MID(A9,2,1)=".",A9,IF(NOT(EXACT(PROPER(LEFT(A9,2)),LEFT(A9,2))),A9,""))&" "&
IF(MID(A10,2,1)=".",A10,IF(NOT(EXACT(PROPER(LEFT(A10,2)),LEFT(A10,2))),A10,""))&" "&
IF(MID(A11,2,1)=".",A11,IF(NOT(EXACT(PROPER(LEFT(A11,2)),LEFT(A11,2))),A11,"")))


Plop that in B1 and copy down. It creates an error on Cushing's Syndrome because it thinks it's the start of a new sentence, but the rest of them it does perfectly. (If you uncapitalize Cushings, it works fine.) Still, with 190,000 rows it's a step in the right direction.

concatenation from hell.xlsx

Thanks you so much. You really helped me, saving 10-15 days of manual work.

thanks for you help, but i've noticed another dilemma. It now adds a part of the next question or answer to the previous. Notice the attached excel file. In C column I demarcated (In red) the unnecessary part that is being added to every question

Maybe an idea.. now that we know where the position of the first line for each question is. Maybe we can extract everything between those first lines and using a new much more simple formula lets say in column C?