I had this strange problem importing a regular .csv file into Openoffice.
A field in the file would not be recognized as a number, no matter what I would do during the import process.
This happened chossing ‘Sheet from file’ as well as cutting and pasting into a sheet.
The ‘ is not visible into a cell but is present only in the formula field.
A simple find and replace will not help. Nor it will help setting a the cell format via Format Cell
Rather than modifing every value by hand, I found this rather handy fix using regexp by a guy named Villeroy on the Openoffice forums. I don’t know why a simple find would not work while this does.
To summarize:
Search for .+ (dot plus=one or more of any char. the apostroph is not part of the value)
Replace with & (the same string that has been found)
[x] Current selection only
[x] regular expression
Replace all