How can I search and replace the ' character inserted into date cells when importing from a text file?

asked 2015-12-04 00:40:00 +0100

GregoryRHill gravatar image

updated 2016-03-12 22:36:24 +0100

Alex Kemp gravatar image

This question relates to Calc Version: 5.0.3.2 Build ID: e5f16313668ac592c1bfb310f4390624e3dbfb75 Locale: en-US (en_US) Windows 7 Professional English

Quicken can create a tab-separated file that is suitable for importing to a spreadsheet. When I look at that output, there are no extra characters added to any of the fields. However, after importing into LO using the "insert>sheet from file" function, the date cells are all preceded by what appears to be the ' character (i.e. lowercase " on my QWERTY keyboard). The presence of the ' makes the cell into text such that when I change the format of the cell to "date", nothing happens because the ' apparently stops the formatting as a date. If I manually remove the ' everything behaves as I would expect.

Fair enough, but it's a pain to remove all of those ' by hand. So, I tried search and replace but I cannot figure out how to get the function to find the ' character. I've tried copying the character from a cell into the search box and still LO cannot find it.

What is the correct incantation/procedure to stop the import process from inserting the ' in the first place, or to get the computer to remove it after the fact?

edit retag flag offensive close merge delete