remove apostrophes before numbers in OpenOffice Calc

The Problem:

You have a spreadsheet that has a column of what you think are numbers in it.  The numbers display fine in their cells, but when you try to do something with them, you find that you can’t.  For example, you may want to sort them or use them as part of a calculation and it doesn’t work.  Here is a screenshot illustrating that the numbers look like numbers in their cells:

apostrophe problem - 01

If you click on one of the cells, you’ll see that the numbers actually have a hidden apostrophe in front of them, like you see in this screenshot:

apostrophe problem - 02

After spending some time on the internet, I finally found out what is going on.  While you think those are numbers, the apostrophe is Calc’s way of saying that the numbers you see are to be treated as text, but they are in a cell that is formatted as a “number.”  You can verify this by right-clicking on the cell, selecting “Format cells” and then clicking on the “Numbers” tab, as illustrated in the screenshots below:

apostrophe problem - 03

apostrophe problem - 04

So, the spreadsheet program is treating a series of number as text in a cell formatted as numbers.  Talk about aggravating.

The Solution:

There is a solution for this.  It involves a “find and replace” function.  From the “Edit” menu, choose “Find & Replace.”  In the “Find” box, type “^.” (i.e., carat + period).  In the “Replace” box, type “&” (without the quotes, of course).  But you have to do one more thing for this to work.  At the bottom of the “Find & Replace” window you’ll see a button that says “More Options.”   Click on it, then select the box next to “Regular Expressions.”  You should now have a “Find & Replace” box that looks like this:

apostrophe problem - 05

Now, hit “Replace All” and all of your apostrophes will disappear.  Your series of numbers will now be seen as numbers by your spreadsheet program and will be in cells formatted as number cells.  Problem solved.

Notes:

I actually don’t know how/why this works, but it does.  I found the solution here.

114 thoughts on “remove apostrophes before numbers in OpenOffice Calc

  1. Pingback: open ofisteki say?lar?n önündeki kesme i?aretini silmek | gurcan yavuz

  2. It works because it’s a regular expression.
    ^ means “at the beginning of the line”
    . means any character.

    so the “find” means to find the first character at the beginning of the line.

    This is replaced with an ampersand, which is the hidden character signifying a number.

    An apostrophe before a number tells Open Office to treat the number as text.

  3. Awesome!
    This have been driving me crazy since installing Open Office on my Mac!
    Now I don’t have to do either of my previous options which were a) edit them all out or b) not sort by date when merging spreadsheets!
    Again, awesome!
    Thanks

  4. What worked for me: (I’m using Kingsoft Spreadsheets but I think this works universally though)

    1) Copy entire column from spreadsheet
    2) Open Notepad (NOT Wordpad)
    3) Word-wrap should be OFF
    4) Paste column into Notepad and save as damn_you_apostrophe.txt
    5) Close Notepad and reopen
    6) CTRL-A then CTRL-C to copy entire contents of Notepad
    7) Paste in empty unformatted column in your favourite spreadsheet app.
    8) Apostrophe’s GONE!!!

    This took me only two days to discover LOL!!!

  5. Awesome, thank you. Caused endless frustration for me to discover, what the hell is wrong with my calculations (which didn’t gave any result because that f***ing pointless apostrophe)

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>