Ask Your Question

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: 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

2 Answers

Sort by » oldest newest most voted

answered 2020-08-24 04:55:34 +0100

Earnest Al gravatar image

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.

image description

edit flag offensive delete link more

answered 2020-08-24 02:40:43 +0100

LeroyG gravatar image

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:

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 (Correct answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-12-04 00:37:17 +0100

Seen: 126 times

Last updated: Aug 24 '20