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

 

[joeu2004]

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.

I don't know what Home > Number does in your version of Excel. In my version, you might be talking about the feature on the ribbon that selects the cell format. Note that it selects a format for displaying values. It does not convert text to numbers, nor numbers to text.

To demonstrate, format A1 as Text, and enter 123. Then change the format of A1 to Number. If we enter =SUM(A1), it returns zero(!) because the type of the value in A1 is still text, despite the cell format.

In order to change the type of the value, we must "re-enter" the value or formula. One way: select the cell, press the key f2 (not the cell F2), then press Enter. (Press ctrl+shift+Enter if the formula is an array formula.) Then =SUM(A1) returns 123.

-----

Besides, the video did not put the text into a cell; so the cell format is not relevant.

The video demonstrates the use of =SUMPRODUCT(--RIGHT(A1:A10)), which always returns a number.

The point of the video is: without double-negate ("--"), RIGHT returns text, even though A1:A10 contains numbers; and SUMPRODUCT ignores text in array or range parameter. Consequently, without double-negate, SUMPRODUCT returns zero.

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

First, again, when you simply change the format from Number to Text, the type of the value is not changed; just its appearance. The type of value remains a number. So even =SUM(A1,10) would return 20.

But if you convert the type of the value to text by "re-entering" (e.g. press f2, then Enter), =SUM(A1,10) returns 10 because SUM ignores text, namely the value in A1.

On the other hand, =A1+10 would still return 20 for the reason explained in the video: any arithmetic operation converts text (that Excel can interpret as a number) to a number. In this case, the arithmetic operation is "+". So we do not have to write --A1.

However, that applies only to arithmetic operations, not comparisons for example. So =A1=10 returns FALSE. In that case, we would need to apply an arithmetic operation to A1. Examples: =--A1=10 or =A1+0=10 or =A1*1=10 or =N(A1)=10.

 

[jasonb75]

What you're doing is changing the format of the cell, not the underlying value.

If the number was entered as text originally, then it will still be text, you need a conversion method such as the one in the video to change it into a valid number before the change of format will work.

There are a number of ways to do it with formulas, or you can use text to columns from the Data tab if you want to do it formula free.

 

[AhoyNC]

He had an array of text {"1","2","9"....}. Home - Number will not change this array to numbers. As he stated an math operation will change text to number (you could add 0 or multiply by 1).
In answer to your second question. you applied a math operation which changed the text to numeric.

 

[RasGhul]

I just did a test and numbers entered as text wouldn't change back to numbers with number format, but --right resulted in values numbers.

+ is a math operator like -- and converts text numbers to numbers

 

[lezawang]

Thank you all. Sorry I was not clear. Let me please explain it again. I have A1=10, A2=20, A3=30. I highlighted A1:A3, then click on Home Tab --> went to Number group. Clicked on General menu, Select Text.

Now I have all these numbers moved to the left side of the cells. Now when I click on B1 and type =Sum(A1), I get 10.

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? Thank you very much.

 

[lezawang]

Can you please tell me why we have this option:

Home tab --> Number group --> click on general group --> Text

Why this is needed? any real life example would appreciate to understand why I need to format as Text. Thank you all once again

 

[Michael M]

Imagine having the number 1234.00001 in a cell.
IF you don't need to apply any mathematical equation to it, but simply want it to appear as text for the visual result, converting it to text will simply show..."1234"

 

[lezawang]

Thank you very much. I inserted 1234.00001 and then changed to Text, but nothing happened. The same number appear, only moved to the left.
Then I clicked on an empty cell and changed the format to Text and then enter the number. The number this time moved to the left but the same number exactly 1234.00001 and this time the cell has green triangle

Thank you very much

 

[Michael M]

What was it formatted as before you typed in 1234.0001 ??