Convert Text to Numbers

Some times you may have imported data that looks like a number but Excel is treating like text. You can usually tell this be lookking at the text alignment. If the text in a cell is left aligned then Excell thinks its text, if it is right aligned (unless you have played with the alignment) then excel thinks its a number.

If you Excel thinks the cell is text rather than a number you won't be able to do arithmetic on the cell and if you try to sort the column it won't sort the column in numerical order.

To convert a column from text to numbers you can using the following process.

• Select the column
• Open the Find & Replace window.
• Enter '.*' (without the quotes) in the 'Search for' input field
• Enter '&' (without the quotes) in the 'Replace with' input field
• Select 'Other  Options'
• Select 'Regular expressions'
• Click Replace All.

Your cells should now be treated as a number.

If you want to know why this works you need to read up on the magic of Regular Expressions.