LibreOffice Calc REGEX to extract multiple choice question/answer strings

user_19687269 0
Viewed 89

This looks like an easy task but I'm not being able to achieve it with LibreOffice Calc REGEX formulas. I have a bunch of exams, each one with 20 multiple choice questions in this format:

1. Which of the following statements about producers is false?
a. Households produce many goods and services for themselves.
b. People set up some producers who do not aim to make profits.
c. All the goods and services consumed in any country are produced by its own producers.
d. Governments arrange the production of some goods and services.

Some questions have several paragraphs with new line characters in between. What I want to achieve is to capture/extract each one of these strings (question / answer a / answer b / answer c / answer d) using REGEX in LibreOffice. The idea is having one REGEX for each string to separate the info in different cells like this: libreoffice sheet example

For now I managed to do the matching with an online regex tester: https://regex101.com/r/jhfr63/1

To capture the question string: (^\d+\.[\S\s]+?(?=^a\.))
And to capture for instance answer "a": (^a.[\S\s]*?(?=^b\.))

I haven't had any luck doing the same with LibreOffice REGEX, so I'd need some help if possible. After changing the above regex the best I could achieve was to extract the question, but it only works for the first match, and I'm not too sure how it works:

=REGEX(A1,"^\d+\.[\S\s]*(?=\b[a]\.)",,1)
libreoffice-calc regex
2 Answers

The problem seems to be the ^ in both of your regexes. This will match only at the beginning of the cell's content, but not after line breaks. For line breaks, you'll have to search for \n. This is the crucial point, since in your use case, the line breaks are delimiting the components of your result.

Try the following (surely not the best solution, but it did work for me).

Assuming that A1 contains the questions/answers, you'll have to put the following formulas into B1:F1 (first formula into B1, second line into C1 and so on):

=REGEX($A$1;"(?<=^|\n)\d\.(.|\n)*?(?=\na\.)";;ROW())
=REGEX($A$1,"(?<=\n)a\..*(?=\n+b\.)",,ROW())
=REGEX($A$1,"(?<=\n)b\..*(?=\n+c\.)",,ROW())
=REGEX($A$1,"(?<=\n)c\..*(?=\n+d\.)",,ROW())
=REGEX($A$1,"(?<=\n)d\..*(?=(\n+\d\.)|$)",,ROW())

Then, just select B1:F1 and pull the formulas down until B20:F20. Since i've made use of the ROW() function, every line will select the nth Question / Answer.

Explanation:

  • As first element, i've used a positive lookbehind (?<=\n), so it matches after a hard line break (only for the Question pattern, i've used (?<=\n|^) since the first question starts immediately at the beginning of the cell.
  • next, i've searched for
    • one or more digits followed by a dot (Column B - questions: \d+\.) or
    • a single letter followed by a dot (Column C:F - answers: a\. to d\.).
  • next, select everything (.*) delimited by a positive lookahead ((?=\n[a]\.)). The lookahead pattern depends on the column:
    • for the question column, look for one or more linebreaks \n+ followed the beginning of the first answer a\.;
    • to find the end of the first answer, look for one or more linebreaks \n+ followed the beginning of the second answer b\.
    • and so on for 3rd and 4th answer.
  • Only for the last answers pattern, i've used a different positive lookahead (?=(\n+\d\.)|$) since after answer 20.d, there's no \n21. to match, so i had to include the end of the cell $ as alternative option.

It would be much better to have a single regex for all of the questions. This might be possible, but it would be quite difficult to achieve (depending on the column number, select one of a-d). Thus, i've used one regex for each of the answers.

user_3425460

Without regex:

enter image description here

Formula in B1:

=TRIM(FILTERXML("<t><s>"&REGEX(SUBSTITUTE(SUBSTITUTE($A$1,"&","&amp;"),"<","&lt;"),"(?<=\n|^)(?=\d+\.|[a-d]\.)","</s><s>","g")&"</s></t>","//s[node()]["&COLUMN(A1)+ROW(A1)*5-5&"]"))

Drag 5 columns right and x-amount rows down untill no more values.

Regex pattern: (?<=\n|^)(?=\d+\.|[a-d]\.) means:

  • (?<=\n|^) - Positive lookbehind to assert position is preceded by newline character or start-string;
  • (?=\d+\.|[a-d]\.) - Positive lookbehind to assert position is followed by 1+ digits or character a-d and literal dot.

Xpath expression //s[node()]["&COLUMN(A1)+ROW(A1)*5-5&"] means:

  • //s[node()] - Any non-empty nodes;
  • ["&COLUMN(A1)+ROW(A1)*5-5&"] - Nested math to return the appropriate index from elements.
user_97581940
Related