With Excel, it is not quite a rare case that numbers starts behaving like text. The behavior is experienced when you fetch data from some source and some of the numbers are unable to acquire the proper number format.

The easiest way to identify the one is by looking at the left top corner of the cell. A numbering behaving as a text is usually:

1. Left aligned (like text) and
2. Have a green triangle showing a notification that number is stored as text

In this short post we will try to learn how to convert such text to numbers.

Method No 01. Using notification handle in the cell:

When we click the notification for such numbers on the left top corner, it gives us option to

You can simply convert it to number by select the second option.

Method No. 02. Using Paste Special Options

You need to copy a blank cell first, and then select the range that has numbers stored as text. Select Paste Special > Operations > Add > OK.

Pressing Ok will convert the text to numbers.

Tip: You can reach the Paste Special Dialogue box by using the key combination: Alt+H+V+S and then tab to reach the Add option.

Method No. 03. Converting using formulas:

If you want to use formula for converting text to number, you can use following two formulas:

VALUE() Function:

This is perhaps the easiest way to get the number out of text. just use this formula and format it the way you want.

TEXT() Function:

TEXT(TextAsNumber,”#”) will also give you a number, but left aligned. The usefulness lies in the fact that you can use the second argument to format your number.

That is all for this post, hopes you will find this post helpful. You see soon!!