Convert number stored as text to number in Excel
A new function in Excel 2013 allows you to convert a number stored as text to number that Excel recognises (and that you can use in your other calculations).
In prior versions of Excel there are ways to do the same thing but they often involve using more than one function. Typically we would use a combination of SUBSTITUTE and VALUE to force a number like
1.000.000,00
to a number that works on our machine of
1 000 000.00
(notice the different symbols that represent thousands and decimals between the 2 versions).
In Excel 2013 there is a new function called NUMBERVALUE that achieves the same thing in one go.
As shown below, you can point at a number that is ‘wrong’ per the format of your machine (a regional settings issue), and tell Excel what symbol is used for a decimal and what symbol is used for the thousands groupings in the ‘wrong’ version.
Excel will then SUBSTITUTE the ‘wrong’ symbols for the symbols that work on your machine and make the cell appear as a VALUE without you having to use these other functions.
