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

[GregoryRHill]

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?

[LeroyG]

Select the date cells, choose menu Data - Text to Columns… - OK.

May be you will need to change some options before pressing OK.

More LibreOffice Help on Text to Columns.

Also can see: How do I get rid of the apostrophe before a number in Calc?

---

Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

Check the mark () to the left of the answer that solves your question.

[EarnestAl]

As LeroyG suggested to fix your dates

When you import your tab-separated .csv from Quicken into Calc there are some Text Import options.

In theText Import window, click on the Date column and in Column Type select the format of the date column in its current format: Date(DMY), Date(MDY), or Date(YMD).

If there is a column of numbers used as symbols, e.g. postcodes, telephone numbers, SKU numbers, etc. select that column and in Column Type change it to Text.

It is worth making sure that Detect special numbers box is also ticked so that currency symbols will not cause problems. I personally would not rely on this setting to give me dates correctly, especially if the imported date is not the same language setting as the default document or the default language in the operating system. It might confuse 11th January with 1st November for example.