numbers to text and text to numbers

[lezawang]

Hi
I just watched a video of excelisfun, the link below and he suggested to change text to numbers by using -- in front of a function Right().

My question, why he did not suggest to go to Home-->Number--> then select number to convert to text to number.

Another question. If I have the following numbers in a column (see below please A1:A3). If I change them from number to Text (Home-->number-->Text). They will be aligned to the left side of the cells but if I do for example C1 =A1 +10, I will get 20?
Why is that when A1=10 is a text not a number

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]10[/TD]
[/TR]
[TR]
[TD]20[/TD]
[/TR]
[TR]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much.

https://www.youtube.com/watch?v=mjLvi0gu494

 

[lezawang]

First, it was general, then I typed the number, then went to Home-Number->Text. It just moved to the left, the whole number.
Then selected another cell, changed the format to Text, then enter the number, again the whole number appears to the left with green triangle in the cell. Thank you

 

[joeu2004]

Imagine having the number 1234.00001 in a cell. [....] converting it to text will simply show..."1234"

But simply changing a numeric format (General, Number, etc) to Text, as Leza is doing, does not "convert it to text".

And the appearance after changing the format to Text depends on how the number was displayed initially.

If we enter 1234.00001 into a cell with default width and formatted as General, yes, Excel displays 1234. Then when we set the format to Text, it continues to display just 1234, but on the left because that is the default horizontal format for Text cells.

Moreover, if we enter 1234.00001 into a cell with numeric format and make the cell wide enough to display 1234.00001, then when we set the format to Text, it continues to display 1234.00001 unless and until we make the cell too narrow.

But look at the Formula Bar. The value is still 1234.00001. Make the cell wide enough, and Excel displays 1234.00001, even when formatted as Text.

The fact is: ISNUMBER(A1) returns TRUE, and ISTEXT(A1) returns FALSE. So the type of the value is still numeric.

 

[joeu2004]

That is what I do not understand. A1 now is a text which looks like 10. Why when I do sum(A1), I get 10? So what is the point of converting it to Text when is still can be used as a number?

I explained all this in response #2 , posted 20 minutes earlier.

First, you are not "converting" to text when you change the format to Text. Proof: ISNUMBER(A1) is TRUE after changing the format to Text.

The appearances (on the left) and the cell format (Text) do not affect the type of the value (numeric).

Is that clear?

That should explain why SUM(A1) returns a numeric value (10) instead of zero. The cell value is numeric. Again, ISNUMBER(A1) is TRUE.

Is that clear now?

----

It is also true that if a cell is formatted as Text and we enter "a number" (numeric text), it will continue to be treated as text after we change the format to General, Number or some other numeric format.

"What is the point?" Sometimes there is no "good" reason. It is simply the way things work in Excel.

Is that clear?

If I had a $1 for every "odd" or "dumb" thing that Excel and other Office products do, I'd be as rich as Bill Gates, too. (wink)

 

[lezawang]

Yes it is very clear. Thank you so much all.