Remove dates from column

Discuss the spreadsheet application
Post Reply
awascholl
Posts: 61
Joined: Wed Dec 12, 2012 9:40 pm

Remove dates from column

Post by awascholl »

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.
Attachments
Remove dates.ods
(24.45 KiB) Downloaded 76 times
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove dates from column

Post by Villeroy »

Select the column.
menu:Edit>Find&Replace...
[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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Remove dates from column

Post by jrkrideau »

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.
Attachments
Remove dates--modifed20190213.ods
(32.1 KiB) Downloaded 69 times
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Remove dates from column

Post by Lupp »

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.
Attachments
SplitEvilCompound_1.ods
(77.94 KiB) Downloaded 64 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply