spreadsheet tweaking

[scrubbyfish]

This thread will grow (well, in terms of my questions, but also hopefully with answers).

I have a very basic knowledge of spreadsheet use.
I generally use OpenOffice Calc, but I have Microsoft Excel, too, if that comes in handier.

Question #1: When I import a spreadsheet, how do I ask it to eliminate an apostrophe before each date reference?

It is invisible in the spreadsheet view, but the dates won't sort. When I clicked on one date cell, the bar at the top showed me there is a secret apostrophe before each date. When I remove that manually, the dates respond to my antics. This will be hundreds of lines per year, though. Is there a way I can ask the whole spreadsheet to not have a secret apostrophe in the date field, either during import or after?

[ShoulderThingThatGoesUp]

Text to Columns on one column, don't delimit it, tell it the column is a date on the last screen.

[johnhenry]

Can you post the file or copy of it with any sensitive info removed?

The "find and replace tool" may be one way.  Do a find/replace, on that column only, and replace  the "X" with "". 

Where X = the character you want to replace and "" is nothing, the absence of any characters.

[scrubbyfish]

Thanks, ShoulderThingThatGoesUp and johnhenry!

Text to Columns on one column, don't delimit it, tell it the column is a date on the last screen.

I just realized this import doesn't bring up the interim window for dictating the format. It jumps straight to the spreadsheet window.

Can you post the file or copy of it with any sensitive info removed?

The "find and replace tool" may be one way.  Do a find/replace, on that column only, and replace  the "X" with "".

Just tried this... Alas, it doesn't find the apostrophes. I guess if they're not visible on the screen, it can't find it.

Is it possible this spreadsheet (from a third party) is set up to limit how I can muck with it?

Almost every column is sensitive, so I don't think I can post it :(

[ShoulderThingThatGoesUp]

You should still be able to run Text to Columns after the import.

[scrubbyfish]

You're right, STTGU! All set now!