top of page

Reformatting text as numbers quickly

A frequent problem in Excel is to end up with numbers in a column that are formatted as text. If this happens, a formula making reference to the values in the column may not perform correctly - it will not find the numbers.

Excel gives you a head's up the numbers are present by appending a green tab to the upper left of the cell. When the cell is clicked on you are given a drop down menu in which one of the options is to convert the text to numbers.

With a very long column data of it may be very difficult to find when the numbers formatted as text begin, so you can select all of the cells you need to convert. The drop down menu will only appear on cells with numbers formatted as text.

To get around doing a lot of scrolling up and down, use this shortcut. Select the entire column and use the Text to Columns tool on the Data tab. When the tool opens, rather than selecting one of the delimiter or fixed length settings, simply click 'Finish'.

All of the numbers in the column will be reformatted as numbers.

bottom of page