Solve leading apostrophe problems

I have a column of figures that have a leading apostrophe(‘). Excel treats them as text. How can I convert them to numbers that Excel will use?

By Neale Blackwood

There are at least two methods. This first involves typing 1 into a cell. Copy that cell. Then select the range you want to convert to numbers. Right click the range and select ‘Special Paste’ and then click ‘Multiply’. Click OK. This is also a handy way to convert positive numbers to negatives and vice versa if you use -1 instead of 1. You can also use the ‘VALUE’ function, which converts text figures to values. Eg = VALUE(A1) where A1 is the cell that has the text figures. You can then use Paste Special>VALUES to paste the formula cells on top of the text figures’ location.
Warning: Numbers with leading apostrophes can cause problems in Excel as the cell appears to contain a number, but Excel’s SUM function will not include them in calculations. Always look at the alignment of the cell contents. Excel aligns text to the left and numbers to the right. If you manually change the alignment it becomes harder to see cells with leading apostrophes
First published: AUSTRALIAN CPA Magazine March 2004 – Page 57 Excel Yourself

Extras

When typing numbers with leading zeroes e.g. mobile phone numbers, typing an apostrophe first will ensure that Excel doesn’t drop off the zero.