Leading apostrophe in statement download

[Theo706]

I am stumped!

When I download a bank statement from the Co-operative Bank numbers and dates start with an apostrophe, visible in the editing field but not in the cell.

When I copy the statement into my spreadsheet dates are not recognised as dates and nmumbers are not recognised as numbers.

This is what a date looks like in the editing field: '11 MAR 24. Numbers look like this: ’ 359.59

These are the methods that I have tried with no success:

1. REPLACE(B2,1,1,""): Using this method removes the first digit of a two digit date. Not what I need.
2. RIGHT(B2,9): Leaves the apostrophe in and '11 MAR 24
3. Using one of the formulae then cutting and Paste Special—> Values only

The only cure that I have discovered is to manually remove the apostrophes.

I would be really grateful if someone could suggest a labour saving method

Nick

[Villeroy]

Simply import your statemets correctly. Check option “Detect special numbers”.

[erAck]

And read this FAQ.

[Wanderer]

@Theo706

When I download a bank statement

If this is some csv, follow Villeroys advice to open the file correctly. If this is the only type of csv you use, your settings should be remembered. Note, you can also change the type of columns


If you download .xlsx or .xls you need to repair the items imported as text. I usually use text-in-columns for this - mentioned in the already linked FAQ last.

[mikekaganski]

Most likely, it wasn’t a problem of “Detect special numbers”, because numbers like 359.59 don’t need that setting to be detected properly. The likely cause is that the import dialog has wrong locale chosen for the import, which disallows detection of the numbers / dates, so they are imported as text, and then, since the program locale (different from dialog’s locale) could convert these texts to numbers, the apostrophes help to see that they weren’t converted.

So - no, in this case, the @Villeroy’s favorite “enable Detect special numbers” incantation doesn’t help (at least as the first and the only step). Make sure to use the correct locale first. Of course, you might want to enable that detection still, either as a “precaution” (since there is a perception that it is generally good to have), or because some specific pieces (like dates) would still not be detected properly.

[Theo706]

Thanks Villeroy. That works beautifully.

The only problem that I had was fiinding the Test Import Dialogue. That’s because the Co-operative Bank downloads the file as .xls so it loaded straight into Calc without shoing me the Import Text dialogue. I needed so resave as .csv then load it into Calc again.

Thanks for all the suggestions and apologies for not having got back earlier.

Nick