Don't try to do this in the PDF file. Export the data as a plain-text file and then it will be much easier to manipulate it in such a way using a plain-text editor that supports RegExp replacements, like Notepad++.
You may not have to do anything: In Access, on the Import dialog, there is an "Advanced" button, that brings you to a dialog for import options. Here you can specify the data format. That should allow you to prevent the problem in the first place.
1 person found this helpful
Thank you Karl, you helped solve my problem; saved me hours of trying to find a work around.
To clarify how it works:-
1 Crop the data needed in the PDF file
2 Delete the unwanted pages
3 Export the cropped data to Excel; BUT save as HTML (Saving as XLSX is no good as the damage is already done)
4 HTML reads the date as Text
5 Access will then allow me to format the text as D/M/Y and also delete the 4 digit year.
Again, thank you very much; appreciated.