Extracting specific string from spreadsheet cell

Question

I've got an LibreOffice Calc spreadsheet and need to extract a small string from a cell in each row. The cells contain about a paragraph of text and look similar to the below but all with different words, lengths etc. The one common thing is the actually the format of the text I need to extract, in this case 17/11/2016 09:00 but could be any date/time formatted like that in 24h format.

Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the 17/11/2016 09:00 industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.

I've searched the following site but can't join it together to work to even match let alone extract the actual string.

http://regexlib.com/DisplayPatterns.aspx?cattabindex=4&categoryId=5

Could someone point me in the right direction please?

Answer 1

The formula:

=MID(A1,SEARCH("[:digit:]{2}/[:digit:]{2}/[:digit:]{4} [:digit:]{2}:[:digit:]{2}",A1,1),16)

The result:

17/11/2016 09:00

The explanation:

• MID grabs part of the text.
• A1 is the cell containing the text.
• SEARCH gets the location of the text to grab.
• [:digit:]{2} looks for two digits. Syntax is at https://help.libreoffice.org/Common/List_of_Regular_Expressions.
• / looks for a literal slash, as does the space () and a colon :.
• 1 starts looking at the beginning.
• 16 is the length of the text to grab.

For this to work, be sure regular expressions are enabled in Tools -> Options -> LibreOffice Calc -> Calculate -> Enable regular expressions in formulas.