5

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?

Question Overflow
  • 1,091
  • 3
  • 13
  • 24

5 Answers5

5

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 enter image description here

  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

Community
  • 1
nixda
  • 27,268
  • Thanks for the detailed answer. I missed out the more options part where RegEx is made available. I adopted another solution that could work for those with version 4.0. – Question Overflow Jul 29 '13 at 02:40
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”.

Scott - Слава Україні
  • 21,717
  • nice one. My question is for LibreOffice, but your Excel solution works perfectly too. The drawback is that I can only select one column at a time. – Question Overflow Jul 30 '13 at 02:00
  • They changed this option in 2010 or 2013. In Excel 2013 Text to columns no longer change the format – nixda Jul 30 '13 at 05:14
1

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)
Question Overflow
  • 1,091
  • 3
  • 13
  • 24
1

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

phuclv
  • 27,773
  • Yes, this work only if there is no formatting in the file. – Question Overflow Jul 30 '13 at 02:03
  • The standard work-around for this is (1) save as CSV, (2) edit the CSV, (3) open in the spreadsheet tool (Calc or Excel), (4) copy the repaired data, and (5) paste values into the original file, overwriting the data that are formatted as you want but are incorrect values. – Scott - Слава Україні Jul 30 '13 at 18:19
1

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.

Doktoro Reichard
  • 5,440
teppscan
  • 11