Convert text to number

How to convert a text to number in Excel when Excel assumes that the number represents text

Related-View our course on cleaning data with Excel

Convert text to number

The Value Function in Excel convert text to number so that what Excel currently sees as text changes into a number. So in Column C I have a bunch of cells that have times in them. In column D I have asked Excel to take the time in Column C and add one hour – in the formula bar it reads =C11+ (1/24). You’ll see that sometimes it works but sometimes Excel gives an error message. The reason for this is that if you look closely at C11, you’ll see that the times that are within one hour have a space, whereas you’ll see in the cell below there are no spaces – it is represented as a number. So we need to instruct Excel that even though there is a space it must not be read as a space but as a number. The function to use is VALUE. You can find it in the Function Wizard. Press OK. Put in C11. Press OK and you’ll see Excel now converts what it previously thought was text into a number. This can be copied down and now every single cell is seen as a number and at this stage you can do whatever mathematical functions you want to it.