How to convert a text to number in Excel when Excel assumes that the number represents text.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
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.