Remove last 8 characters in a column

[Arunakumaar]

I have date and time in a column and would like to remove time alone. Example: 2019-08-09 1:34:34 . I want to remove 1:34:34, that is hrs:mins:secs. I am absolute new to LibreOffice. Assistance is much appreciated.

I have over 100,000 rows under a column with both date and time. I would like to remove the time alone. Example: 2019-07-10 12:15:05 . I want to remove hrs:mins: secs and want only the date, that is yyyy-mm-dd. I am absolutely new to LibreOffice. Assistance is much appreciated.

[Ratslinger]

Please keep main question short. Moved excess to description area & changed tag.

[anon73440385]

Hello,

perform the following (skip the following 5 steps - if all your dates are already numbers)

• Select the column (click on the column header; e.g. on letter A to select column A)
• Go to Data -> Text to Column
• Select dropdown Column type YMD
• Click the square marked red in the screenshot 1) below
• Click OK

Your dates should get right aligned after the previous 5 steps (or they are already right aligned, if the were numbers before and you skipped the 5 steps above). Now you need to format the cells, to show what you want:

• Select again the column (click on the column header; e.g. on letter A to select column A)
• Go to Format -> Cells -> Tab: Number and select Category Date
• Add Format Code YYYY-MM-DD (see Screenshot 2)
• Click OK

Screenshot 1)

Screenshot 2)

Note This answer assumes you want to stay with real dates in your document and does not consider dates to be treated as text. If you want to have text looking like dates, another procedure would be required. But due to the fact, that in almost all cases some calculations using dates are intended, treating dates as text is a bad idea and that’s why this answer focus on real calc dates (which are numbers).

Tested using LibreOffice:

Version: 6.3.4.2,Build ID: 60da17e045e08f1793c57c00ba83cdfce946d0aa
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US,Calc: threaded

Method 2 (Use only if you really want to stick with text)

• Select the column (click on the column header; e.g. on letter A to select column A)
• Go to Edit -> Find & Replace and use
  Find: ([:digit:]{4}-[:digit:]{2}-[:digit:]{2}).*
  Replace: $1
  Other Options [x] Current selection only
  Other Options [x] Regular expressions

Hope that helps.

If the answer helped you to solve your problem, please click the check mark () next to the answer.

[Arunakumaar]

Thank you so much. It worked really well. :))

P.S. I hope you can answer my other question for filling empty cells under a column with the name that is available on the top. Copy, pasting them for over 100,000 rows looks like it will take a very long time.

[Ratslinger]

@Arunakumaar,

As you have been helped, please help others to know the question has been answered by clicking on the in upper left area of answer which satisfied the question.