left("123-456-7890",3)

[lezawang]

Hi
A1=left("123-456-7890",3)

The result A1 = 123 (but as a text, aligned to the left side of the cell). I changed the format to number, general etc it is still text.

I learnt if B1=1 and I copy B1 and Paste Special (multiple) A1 then A1 will be 123 as a number (aligned to the right).

I also tried before doing the paste special to do this C1=A1-1 (the result was 122) correct!!

My question, if the result of left() is a text but excel still can understand it as a number, then why I need to change the format? or what the issue I might have in the future if i kept the result of left() as it is?

Thanks you.

 

[wideboydixon]

=left("123-456-7890,3)+0

wbd

 

[RoryA]

It will depend what functions you use it with. Most number functions, as opposed to operators like +, will ignore text. If you want a true number, use:

=left("123-456-7890",3)+0

 

[JoeMo]

If you want to coerce a number with your formula try:

=LEFT("123-456-7890",3)+0

 

[lezawang]

Thank you all for your reply and help. adding 0 is good idea. But now I have a question

the result of A1=left ("123-456-7890",3) is 123 (as a text)

but when I do B1=A1-1, I got B1=122 (so what is the problem? that is my question why I should bother to change the text to number format when excel will understand 123 as a number anyway? Thank you.

 

[Scott T]

If you do something like a vlookup then the number 123 does not = 123 as text.

Excel 2010
	A	B	C
3	123-15	123	1
4		156	2
5
6
7	123	#N/A	123 is number vlookup does not find 123 as text
8	123	1	123 as text finds 123 as text
Sheet3

Cell Formulas
Range	Formula
B3	=LEFT(A3,3)
B7	=VLOOKUP(A7,B3:C4,2)
B8	=VLOOKUP(A8,B3:C4,2)

 

[Rick Rothstein]

Thank you all for your reply and help. adding 0 is good idea. But now I have a question

the result of A1=left ("123-456-7890",3) is 123 (as a text)

but when I do B1=A1-1, I got B1=122 (so what is the problem? that is my question why I should bother to change the text to number format when excel will understand 123 as a number anyway?

Excel is always trying to help (sometime detrimentally as with values that look like dates but are not meant to be dates). In the case of text values that "look" like numbers, Excel will convert such a text value to a real number in order to perform the indicated math operation. That is why adding 0 to the LEFT function makes the text value returned by the LEFT function a real number. A1-1 is does the same thing except is math operation is to decrement the "number" in cell A1 (adding 0 to the LEFT function does not increase or decrease the value returned from the LEFT function).

 

[RoryA]

If you have say three cells like that, =A1+A2+A3 will return a correct total, but =SUM(A1:A3) will return 0.