0

I want to find a string like a DOI in a sentence and extract the result in a cell. I found the »regex« function in LibreOffice Calc (current Ver. 6.2.4.2) which tells me that it would exactly do what I want.

Example:

Foo bar doi: 10.1016/j.cmpb.2018.09.006 baar foooo

My regex in python is: \b(10[.][0-9]{4,}(?:[.][0-9]+)*\/(?:(?![\"&\'<>])\S)+)\b and suits me well in a python script (it matches exactly the doi number without the »doi« string, which is intended).

But the »regex« function refuses this regex. The documentation mentions the ICU Regex Expressions, where I can't find any issues comparing with my regex.

Do I miss something? Which »regex dialect« is applicable for LibreOffice?

  • 1
    According to RegEx101 your RE is invalid in Python - it needs a back-slash before the double-quote. Maybe your Python release is more tolerant, but it's worth trying with the added back-slash in LibreOffice Calc. – AFH Jun 25 at 21:37
  • Thanks! I've tested the string with ReggexBuddy 4 and it didn't complain. Tried RegEx101, made it fully compatible with python but no luck yet. Seems I miss something crucial. – ptr2n0 Jun 30 at 9:59
1

In the list of regular expressions supported by LibreOffice, most of the standard expressions are supported, but two of the elements you use are missing:

  1. the use of ?: for a non-capturing group;
  2. the use of ?! for a negative look-ahead.

                      1              1  2
                      |              |  |
                      v              v  v
    \b(10[.][0-9]{4,}(?:[.][0-9]+)*/(?:(?!["&'<>])\S)+)\b
    

I don't know what level of RE this represents (it seems to be BRE with additions), but it means that your RE won't match your test string.

It is not clear how much of your string constitutes the number you are trying to isolate, so I cannot suggest how you might avoid the unsupported elements, but these are a couple of possibilities:-

  • If you need the numeric string between doi: and /, then the simpler \b10[.][0-9]{4,}(?:[.][0-9]+)* will suffice.
  • If you need everything between doi: and the next space, then you can use \b10[.][0-9]{4,}(?:[.][0-9]+)*[^\s]*.

(This second case gives the same match as your RE on RegExr.)

I realise that your live data may have more exceptions that must be caught, but without a full knowledge of your data and the number you are trying to match I cannot do more.

  • 1
    Thank you for your answer. Meanwhile I've recognised that LibreOffice Calc is perfectly fine with my regex but had problems with the double quote. To resolve that issue I need to put it directly in a cell and reference it, or – as a string in the formula –I have to use the quote symbol twice and escape the first double quote The DOI is the whole string from doi: to the next space. I will create an answer to my question for this specific solution. – ptr2n0 Jul 1 at 14:30
  • OK. I was testing with =MATCH(), but this was telling me whether the RE matched, not which part of the string made up the match. I wonder why ?: and ?! are missing from the documentation. – AFH Jul 1 at 15:04
  • I have now found a more definitive link to the RE features supported (which includes ?:, ?! and similar constructs), so I shall withdraw my answer once you have submitted yours, since the RE link it quotes is incomplete. Note that this link shows some limitations on RE matching when texts contain cell references. – AFH Jul 1 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.