Can I remove an alphabetic suffix from a column of numbers in Calc?

I have a list of about 800 entries counting in the millions, i.e 3.4m 1.2m etc but when I try to list by numerical order it counts down from 9.9 to 0 then from the highest double digit number down to 10.0 rather than just highest down to zero. I think the problem is that the numbers are not formatted as numbers but as text so I need change them to numbers and need to remove the [m] suffix from every entry (I didn’t add it).

Is there a way to do this without going through 800 numbers by hand?

Before modifying data (which may be done using Find & Replace with regular expressions, and even without them just by replacing “m” with nothing in a given column - just make sure to format the column as numbers prior to the replacement), check if using DataSort dialog, and checking “Enable natural sort” checkbox on Options tab, could resolve it. This may be better in case, when you actually don’t intend to do calculations on these cells, just sort by them.

image

2 Likes

The natural sort solved it, thanks very much, you just saved my afternoon!