Convert Numeric Text in Cells to Real Numbers in LibreOffice

Occasionally I get a spreadsheet with numbers that are presented as characters in a column. You can tell because the numbers will be left-justified in the cells. This can wreak havoc and waste time when trying to filter and sort data. There are several solutions. You can do a find-and-replace, use the VALUE function, or use a plug-in. The one I found is called CT2N. Once installed, it puts an icon on a tool bar. I had mixed results with it so far.

Select the data you want to modify.

For find and replace, key in ^[0-9\-] into the “Search For” box.

In the “Replace With” box, key in a single ampersand, &.

I usually click on “Replace All” since I have selected the exact I want to modify.

Now you can proceed to filter and sort your data to meet your needs.

This link was helpful for using the find-and-replace and VALUE function techniques.

https://neowiki.neooffice.org/index.php/Converting_a_Text_String_to_a_Number_in_Calc

This link instructs you on installing and using the CT2N plug-in.

https://extensions.libreoffice.org/extensions/ct2n-convert-text-to-number-and-dates

Hope this helps and good luck!