[Solved] Removing single quote from numbers

[gardener41]

Somehow when I imported some data, a single quote mark was placed in front of the numbers so they appear to be text instead of numbers. How do I remove the quote on a mass scale. (editing each cell would take forever!)

[acknak]

You can remove the quote with Edit > Find & Replace

You can also configure the text import to handle the conversion. The usual culprit is a CSV/text file that has double quotes around every field. OO Calc normally takes double quotes to indicate text values; text that could be interpreted as a number is displayed with the quote prefix to show the text value type and prevent the text from being converted to a number. If you turn off the "Quoted field as text" option, OO Calc should import the numbers directly as numbers even if they have quotes around them.

If that doesn't help, or you want some more specific suggestions, we'll need to have a sample of the text data you're working with.

[gardener41]

I tried to FIND with '
and REPLACE with a blank space
It gave me SEARCH KEY NOT FOUND

[acknak]

Sorry, it's a bit more complicated than I remembered.

1) Select the cells you want to change
2) Make sure the cell format is NOT text: Format > Cells > Numbers > Category: Numbers, Format: General (any numeric format will work)
3) Edit > Find & Replace
Search for: .* (that's two characters: period, asterisk)
Replace with: &
Options/Current selection only: ON
Options/Regular expressions: ON

Click Replace All

[erbsenzahl]

Somehow when I imported some data, a single quote mark was placed in front of the numbers so they appear to be text instead of numbers.

If your data are in column order so use:
Data menu > Text to columns
The easiest way to get rid of the single quote marks.

Sometimes the following proceeding works: Cut your data out of the sheet and insert as unformatted or formatted text (use arrow/triangle beside the paste icon).

Or proceed with acknak's tips, they work great.

[Villeroy]

It's always preferable to import numbers correctly. Always turn on the "special numbers" option.

[anzon]

YES IN BIG LETTERS FOR PEOPLE LIKE ME

TEXT TO COLUMNS

MANY, MANY THANKS

I think a lot of people have been getting the "leading-apostrophe-itis" problem and causing themselves and respondents a lot of unnecessary care and woe.
Could the OO help section have the "leading apostrophe" as a suggestion on its own page?

[Villeroy]

This is the most basic spreadsheet knowledge since the 80ies. Every old Excel book describes the difference between the number 123 and the text "123" within the first chapters. This is essential to know before you start editing a spreadsheet document.
Using the find tab in the F1-help, "apostrophe" finds 3 relevant chapters mentioning the apostrophe in the context of numeric text.