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.
Attachments
Remove dates.ods
(24.45 KiB) Downloaded 17 times
OpenOffice 4.0.0 on Win7
awascholl
 
Posts: 40
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.
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26635
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.
Attachments
Remove dates--modifed20190213.ods
(32.1 KiB) Downloaded 8 times
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3682
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.
Attachments
SplitEvilCompound_1.ods
(77.94 KiB) Downloaded 6 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2412
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 17 guests