convert number stored as a text into number

[lezawang]

Hi
I watched a youtube channel and he covered the numbers below ( I created the same numbers) into number by copying number 1 and then PasteSpecial --> multiply. It did work and all numbers moved to the right hand side of the cells after that copy and pastespecial. But before I pastespecial, I tried it to create simple formula to add each of these number to 100. I got correct answer. My question, why I need to do the copy 1 then pasteSpacial-->multiple, when I can get the right answer without that step? Thank you very much.

numbers hosted at ImgBB

Image numbers hosted in ImgBB

ibb.co

 

[AhoyNC]

Because you will not always get the right answer. If you do a mathematical operation on a single cell it will work.
However in the example below you will see the SUM function in cell A5 didn't work. When I used the VALUE function or added each cell individually I get the right answer. If you had 200 numbers formatted as text you would not want to have to type in each cell.
Also, if you use a function like VLOOKUP and the lookup cell is text and the the value to lookup is entered as a number you will get an error.

Book1
	A	B	C	D
1	Text
2	1	Test1		#N/A
3	2	Test2		Test2
4	3	Test3
5	0
6
7	6
8	6
Sheet1

Cell Formulas
Range	Formula
D2	D2	=VLOOKUP(2,A2:B4,2,0)
D3	D3	=VLOOKUP("2",A2:B4,2,0)
A5	A5	=SUM(A2:A4)
A7	A7	=SUM(VALUE(A2:A4))
A8	A8	=A2+A3+A4

 

[kanadaaa]

The same as in #2 happens when it comes to VBA codes:

Sub Fill999AndAdd100()
    Dim num As String
    num = "'999" 'A single quotation before the number thus outputted as a text string
    Range("A1") = num + 100 'Error: Type mismatch
    Range("A1") = Val(num) + 100 'No error
End Sub