How to convert text to numbers for all cells in a spreadsheet?

Question

I have a spreadsheet containing values that are entered as text by having an apostrophe ' in front. I tried the search and replace function, but for some reason it couldn't find those cells containing an apostrophe in front. I tried changing the cell format to number, but it didn't cause those text to become numbers.

Because there are many cells like that, it would take a long time to individually delete the starting apostrophe in each cell. Is there a faster way to do this?

Answer 1

I found out these things

1. Use LibreOffice version 3.6.5 instead of 4.0.
   The newest LibreOffice 4.0 (see changelog) changed its RegEx engine to ICU regexp engine. As a result I wasn't able to use the LibreOffice 3.6.5 solution for LibreOffice 4.0

   ^{(Maybe this has changed since this answer was given in 2013. So try out the newest available version first)}

2. Disable Tools » AutoCorrect Options » Localized options » Single quotes.
   This option replaces your single quote with a similar looking apostrophe. Unfortunately this doesn't change your existing cells

   The screenshot demonstrates the visual difference with and without this option enabled

3. To replace cells with leading single quote: ' (U+0027):

   Press CTRL+H » Click More options » Check Regular expressions.
   Search for ^.*$ and replace with &

   It searches for everything inside a cell and replace the cell content with the existing cell content - but without the leading single quote since you disabled the AutoCorrect option. Have a look at the reference if you want to read more about Regular Expressions

4. To replace cells with leading apostrophes: ‘ (U+2018) or ’(U+2019) or ʼ (U+02BC):
   ^{Which character is used depends on where you live and what local settings are used}

   Press CTRL+H » Click More options » Check Regular expressions.
   Search for ^[‘’ʼ] and replace with nothing.

---

Alternative

Copy & Paste the whole spreadsheet content to Notepad or Notepad++, do the Search & Replace and copy the content back.

Used Resources

• How to remove a plain text protecting single quote from all the selected cells in LibreOffice Calc?
• http://en.libreofficeforum.org/node/5775#comment-24393
• http://en.libreofficeforum.org/node/484
• https://help.libreoffice.org/Common/List_of_Regular_Expressions

Answer 2

A solution that works in Excel (at least 2007) is to go to the “Data” tab and select “Text to Columns” (from the “Data Tools” group).  The defaults seem to be pretty good, so click on “Next”, “Next”, and “Finish”.

Answer 3

nixda's answer work for me. But I solve my problem using another method. This is likely to work for those with any LibreOffice version.

1. Open the spreadsheet
2. Create a working area (example C1-D10) to the right of the table of data (example A1-B10)
3. Multiply the table of data by 1 (example C1 = A1*1, drag to fill up cells C1-D10)
4. Copy the values in the working area back into the table of data
   (Edit -> Paste Special -> Selection -> Numbers)

Answer 4

If there's no format in the file then you can save the file as csv, open it with any text editor and replaces all the apostrophes

Answer 5

Nixda's alternate did it for me. I had one column of numbers that had gotten text and numerical cells all mixed together and when I converted all to numbers I got the apostrophes that prevent proper sorting. I did the following:

1. Copied that entire tab to another empty tab (to be safe)
2. Cleared all formatting in the column of numbers
3. Selected and copied the range of cells in the number column that had values
4. Pasted the clipboard (numbers from the column) into EditPlus (my text editor)
5. Selected and copied that list of numbers to the clipboard in EditPlus
6. Went back to the Calc tab I was working in and cleared all content from the number column
7. Selected the top cell in that column and pasted the contents of the clipboard Voila -- all are numbers, no apostrophes.

In short, as Nixda said, copy the column into notepad or other pure text editor, and then copy that data and paste it back. Now all I've got to do is make sure no new data is getting formatted incorrectly.