How to convert a text to number in Excel when Excel assumes that the number represents text
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.
Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading
A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading
“Data accuracy and consistency has improved dramatically across Absa Capital, so much so that it has become compulsory within certain areas for all staff to complete Adrian’s Data Analyses Spreadsheet Techniques course.”– Absa Capital