is there any formula to break a line from a specific text?-CodePudding

is there any formula to break a line from a specific text? e.g.

Q.1 This is Question number one? Ans. True

Q.2 This is Question number Two? Ans. false

i have above text where first part is Question and the second part is Answer. This text is in a cell and i want a formula to break the line from "Ans." text to next line without overwriting the text in the next line.

output should look like this:

Q.1 This is Question number one?

Ans. True

Q.2 This is Question number Two?

Ans. false

CodePudding user response：

What about:

Formula in C1:

=DROP(REDUCE(0,A1:A2,LAMBDA(a,b,VSTACK(a,{"";"Ans."}&TEXTSPLIT(b,,"Ans.")))),1)

Just so we avoid TEXTJOIN() and thus it's limits.

CodePudding user response：

You can use a formula like this, if you are using MS365,

• Formula used in cell C1

=TRIM(TEXTSPLIT(TEXTJOIN("@",,SUBSTITUTE(A1:A2,"?","? |")&" |"),"@"," |",1))

Edit :

Another alternative way:

• Formula used in cell E1

=LET(_string,SUBSTITUTE(A1:A2,"Ans.","| Ans."),
TOCOL(HSTACK(TEXTBEFORE(_string," | "),TEXTAFTER(_string," | "))))

CodePudding user response：

You can use this formula to have the answer in a new line in the same cell - if e.g. the text is in A1

=SUBSTITUTE(A1," Ans.", CHAR(10) & "Ans.")

Don't forget to set textwrap for the cell :-) otherwise you won't see the result.

CodePudding user response：

This is far from perfect, but if your list of question and answers in located in column A, and they ALWAYS have "Ans." (case-sensitive), then if you auto-fill the following formula (double the current row count) into an empty column it should give you question and answer on the next row.

=IF(ROW()/2=ROUND(ROW()/2,0),TRIM(MID(INDIRECT("A"&ROUND(ROW()/2,0)),FIND("Ans.",INDIRECT("A"&ROUND(ROW()/2,0)),1),1 LEN(INDIRECT("A"&ROUND(ROW()/2,0)))-FIND("Ans.",INDIRECT("A"&ROUND(ROW()/2,0)),1))),TRIM(MID(INDIRECT("A"&ROUND(ROW()/2,0)),1,FIND("Ans.",INDIRECT("A"&ROUND(ROW()/2,0)),1)-1)))