This article was long pending after I wrote Covert Number Format into Text Format.
This post essentially deals with the fact that if a cell has number in text format, how to convert that value into Number format so that it becomes usable for calculations and for other purposes. (Exclusion – If a number has been entered as accounting format 12- i.e. not with leading minus sign, this article doesn't cover this)
Once again, we will look into this problem statement from 4 angles –
1. Formula Way
2. Manual Way
3. Hybrid Way (Both Manual and formula ways combined together)
4. VBA Way
But first, after conversion, how will you know the cell value is converted into number or not. You can use any one method from below –
1. Numbers are aligned right and Texts are aligned left by default unless you change the alignment. Hence, after conversion, you will notice that left aligned text is changed to right aligned number (which is number now, in fact). {Note – This is not a foolproof method as alignment can be manually tweaked}
2. Use the function ISNUMBER. If converted Number is contained in B1, use formula = ISNUMBER(B1). If result is TRUE, it is Number. This is the best and foolproof method as it doesn't depend upon alignment.
3. Use the formula N. If converted Number is contained in B1, use formula = N(B1). If B1 is Number, result will be B1 itself. If not Number, result will be 0. Hence, if 2.23 is Number, result will be 2.23. But if 2.23 is not Number, result of T will be 0.
4. Most of the time, before conversion, you will notice a very small green triangle in top left corner indicating that this is a text.
If you select the cell, you will get a warning sign next to it which tells you that this is a number stored as Text.
After conversion to Number, this small green triangle in top left corner will away.
1. FORMULA WAY
1.1 =A1+0
1.2 = A1*1
1.3 = A1/1
1.4 =A1-0
1.5 =–A1 (this is my favourite method which uses double unary operator. First minus forces the text to a number and second minus negates the sign impact of first minus thus reverting the number to its original sign)
1.6 =VALUE(A1)
1.7 = SIGN(A1)*ABS(A1) (This is for academic purpose only. Nobody should use this as there very simple formulas like –A1 are available)
2. MANUAL WAY
2.1 Error Correction Way – If a number is stored as Text, then a green triangle will appear in the top left corner.
If you select the cell, you will get a warning sign next to it which tells you that this is a number stored as Text.
You can select Convert to Number to convert this to Number.
If your text numbers are in a range, you can select the entire range and in first cell of the range, you can select Convert to Number to do conversion for entire range.
2.2 Paste Special Way -This is the most useful part of this Article.
1. CTRL+C in a blank cell.
2. Select the range of cells which you want to convert from text to number. Right Click > Paste Special > Select Add and OK (Note – You can select Subtract also in place of Add. The net effect is A1+0 / A1-0 as given in formula way.)
3. Now, all text numbers will be converted into numbers.
Note – Another variation is to put 1 in a cell and follow steps 1 to 3 but select Multiply or Divide. It simulates A*1 / A/1 as given in Formula way.
The technique is pretty good for bulk conversion in one shot without the use of a formula.
2.3 Retyping Way – If the problem is of very few cells, you can just simply retype the same number. It will immediately make text number to number. Not recommended for many cells but quickest for a cell or two.
2.4 Text to Columns Way – Good for large scale conversions in a single column.
1. Select the column (or range is a single column) where conversion is warranted.
2. Data tab > Text to Columns > Press Finish on first screen itself.
3. Your text numbers will be converted into numbers now.
2.5 F2+Enter Way – Good for few cells.
If your text number is entered in a Text Formatted Cell (sometimes numbers are entered with leading apostrophe, in this case this method will not work. This method works only if cell itself was formatted as Text)
Select that cell, press F2 followed by Enter. The text number will be converted into number now.
Limitation is that this will work one cell at a time.
3. HYBRID WAY
I am unable to conceptualize a way where both formula and manual way together will be useful.
4. VBA WAY
VAL function in VBA can be used for this purpose. A quick code would be for one cell conversion
Sub TextToNumber()
Range("A1").Value = Val(Range("A1").Value)
End Sub
Note – Range("A1").Value = Val(Range("A1").Value) can be replaced with Range("A1") = Range("A1").Value
Hence, a possible code could be
Sub TextToNumber()
Range("A1:D100") = Range("A1:D100").Value
End Sub
You can also create Macros by using Techniques described by me in Formula / Manual ways and use this Macro whenever needed. This all depends upon your need.