Page 1 of 1

Remove dates from column

PostPosted: Wed Feb 13, 2019 6:53 pm
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.

Re: Remove dates from column

PostPosted: Wed Feb 13, 2019 6:59 pm
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]

Re: Remove dates from column

PostPosted: Wed Feb 13, 2019 7:31 pm
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.

Re: Remove dates from column

PostPosted: Wed Feb 13, 2019 9:52 pm
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.