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. Note that it selects a format forcell formatvalues. It doesdisplayingconvert text to numbers, nor numbers to text.not

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 theof the value in A1 is still text, despite the cell format.type

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.

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

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: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.any arithmetic operation

However, that applies only tooperations, 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.arithmetic

## Like this thread? Share it with others