Remove dates from column

Discuss the spreadsheet application

Remove dates from column

Postby awascholl » Wed Feb 13, 2019 6:53 pm

I have downloaded information from a bank account into CSV format and all of the transactions have a date along with the name of who was paid. Can I delete anything that's a date? This will help me sort out the transactions by the name once it's done.

Sample attached.
Remove dates.ods
(24.45 KiB) Downloaded 22 times
OpenOffice 4.0.0 on Win7
Posts: 42
Joined: Wed Dec 12, 2012 9:40 pm

Re: Remove dates from column

Postby Villeroy » Wed Feb 13, 2019 6:59 pm

Select the column.
[More Options]
[X] Current selection only
[X] Regular expression
Search: ^[:space:][:digit:][:digit:]/[:digit:][:digit:][:space:](.+)$
Replace: $1
[Replce All]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 28571
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove dates from column

Postby jrkrideau » Wed Feb 13, 2019 7:31 pm

Yes we can do it. You need to use a set of text manipulation functions.

Left(), Replace() will do what you want. See the attached file for an example.

Would you mind uploading the original .csv file? It may be possible to read the csv file into AOO and avoid the text manipulation.
Remove dates--modifed20190213.ods
(32.1 KiB) Downloaded 16 times
LibreOffice; Ubuntu 20.04
Posts: 3788
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Remove dates from column

Postby Lupp » Wed Feb 13, 2019 9:52 pm

I would dare to add:

Avoid leading and trailing spaces.
Do NOT remove any relevant information from compound data but split them into their meaningful parts.
Change to a bank offering transaction data in a reasonable format. (They can!)
Don't use terms like "sort out" in a technical context. (Not clear/unambiguous enough.)

If the date and the last part (reference number or something similar) of the compound have constant lengths as "jrkrideau" assumed, his solution is simple and basically recommendable, and can still be a bit more simplified.

"For principal reasons" I also provide two solutions not relying on that assumption. The second one only is supported by LibO V6.2.0 or higher.
(77.94 KiB) Downloaded 14 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
Lupp from München
User avatar
Posts: 2918
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Return to Calc

Who is online

Users browsing this forum: No registered users and 30 guests