A cell formatted as text doesn't behave as such.

[TimmyThomas]

Greetings to all of you. I'm experiencing a strange behaviour on Microsoft 365 Excel. When I format a cell, e.g. A1 as text and type 15 in it, then type 5 in A2 without formatting the latter and then write the formula as =, select A1, type + then select A2 and hit Enter, it provides the answer as 20 even though it warned me that A1 is formatted as text. When you delete the formula and redo it, is only then that it gives answer as =A1+A2. When you delete the formula again in A3 and select the Ʃ AutoSum it select A2 only and hitting Enter provides answer as 5. So it ignored the value in A1 which is 15 simply because the cell was formatted as text. But in the first instance it didn't ignore it but gave 20 as the answer. When you reformat A1 back to General is only then that it regards it as text and the answer would be =A1+A2. Can someone please help?

 

[AliGW]

Whether a cell is formatted as text or a number will not affect the underlying value. Please provide a small sample workbook so that we can see your tests in situ.

 

[Debaser]

The unary operator + will coerce numeric text to a number. SUM() will not. Excel also (annoyingly) tries to be helpful by applying a text format to the formula cell (after initial evaluation) to match the format of the first input, which is why editing the formula after the initial entry leads to the cell displaying the formula, not its result.

 

[TimmyThomas]

Whether a cell is formatted as text or a number will not affect the underlying value. Please provide a small sample workbook so that we can see your tests in situ.

I hope these attachments will help to convey the message clearer.

 

[TimmyThomas]

The unary operator + will coerce numeric text to a number. SUM() will not. Excel also (annoyingly) tries to be helpful by applying a text format to the formula cell (after initial evaluation) to match the format of the first input, which is why editing the formula after the initial entry leads to the cell displaying the formula, not its result.

I hope these attachments will help to convey the message clearer.

 

[AliGW]

Why two spreadsheets when you could have put everything into one? Why attach them twice?

You have your explanation in post #3.

This will coerce the whole range:

=SUM(--D1:D2)

 

[TimmyThomas]

Why two spreadsheets when you could have put everything into one? Why attach them twice?

You have your explanation in post #3.

This will coerce the whole range:

=SUM(--D1:D2)

Thanks so much.

 

[TimmyThomas]

The unary operator + will coerce numeric text to a number. SUM() will not. Excel also (annoyingly) tries to be helpful by applying a text format to the formula cell (after initial evaluation) to match the format of the first input, which is why editing the formula after the initial entry leads to the cell displaying the formula, not its result.

Highly appreciated.

 

[TimmyThomas]

The unary operator + will coerce numeric text to a number. SUM() will not. Excel also (annoyingly) tries to be helpful by applying a text format to the formula cell (after initial evaluation) to match the format of the first input, which is why editing the formula after the initial entry leads to the cell displaying the formula, not its result.

Highly appreciated.

 

[TimmyThomas]

The unary operator + will coerce numeric text to a number. SUM() will not. Excel also (annoyingly) tries to be helpful by applying a text format to the formula cell (after initial evaluation) to match the format of the first input, which is why editing the formula after the initial entry leads to the cell displaying the formula, not its result.

I revisited your reply and is only now that it clicks. Thanks a lot.