I have to ask this question every year because it’s so difficult to do and the answer disappears: How do I convert a LibreOffice Calc column of numbers into number format? The numbers appear as '12.34 with (as anyone can see) a single apostrophe prepended to the number and the numbers don’t calculate until I remove the apostrophe. I’ve never found an easy way to get rid of the apostrophe so I can format the numbers as numbers without a lot of hair-pulling. It’s common annual chore, but extremely time consuming and tedious. (BTW, Excel does it with a single key-stroke but we don’t have Excel).
You could select the column and click Data > Text to Columns
OR you could
- Select the column(s) in which the
digits are found in text format. - Choose Edit > Find & Replace (Ctrl+H).
- In the Search for box, enter
^. - In the Replace with box, enter
& - Check Regular expressions.
- Check Current selection only.
- Click Replace All.
Cheers, Al
Thanks! My question is always,“How do you know that?” How does a simple spread-sheet user know how to do this? (So I don’t have to keep coming back here year after year with the same question) In Excel, it’s in the “Format>Cell” function but Libre Office doesn’t have this tool, it’s only a Regular Expressions action - which is not for ordinary folks. Or is this “How-to” hidden away in the main menu somewhere?
It starts with importing the text data and making sure each column has the correct format for the data type it is importing. If this is correct then the data will import nicely. You might need to tick the special formats if there is something different.
The List of Regular Expressions and the linked pages is useful. Of course, you can always search Calc apostrophe on a search engine and get many results.
The simple Data > Text to Columns works well in most instances. Cheers, Al
The Data > Text to Columns doesn’t work for me, likely because the prepended apostrophe doesn’t appear until AFTER I try to format the cells. It’s not visible before-hand, only after Format>Cells is attempted. Still I’m curious: why doesn’t LibreOffice (or OpenOffice) have any instructions on how to do this? I’m not a programmer, so the Regular Expressions thing looks pretty exotic, like something a space scientist would use. I’m only a book-keeper. It makes LibreOffice look extremely primitive when it can’t do something basic like this. What am I missing?
The Text to Columns does deal with the apostrophe, it is one of its functions. There is a tutorial on the OpenOffice forum.
Maybe you imported a different format at the same time. I expect that you have copied and pasted from another document with different language or format settings. Instead of just pasting, maybe Edit > Paste Special > Paste Special and unticking the box Format. That should strip out currency symbols that aren’t recognised for your locale.
Excellent tutorial! I’ve been struggling with Open/Libre/Office for 20 years, not knowing that there’s Tutorials explaining how to do things! That was very helpful, exactly what I need. It’s interesting that all the other replies over the years never mentioned that there’s tutorials. And when I look through L/O itself, there’s no mention of tutorials. Is it because it’s OpenOffice, not LibreOffice? Regardless, if there’s more tutorials of this quality, please let me know in a reply - and where to find them, of course.
Well, there are the breadcrumbs at the top of that OpenOffice page that will take you back to Tutorials > Calc or further back to just Tutorials.
The Calc guide is useful for explanation compared to Help which gives the bare minimum.
This page on the wiki has links to tutorials and other resources. Cheers, Al