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:00industry'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?
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:
A1 is the cell containing the text.[: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 (:.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.