CHALLENGE: Why can Excel still perform calculations on numbers stored as text???

theexcellife

New Member
Joined
Dec 10, 2017
Messages
1
Whether I convert a number to "text" format, or use the apostrophe to indicate a text string that consists of numbers, I can still perform calculations on those strings. Why is that?

Even crazier, I changed the number converted to "text" format to a string of letters, and my IF function still executed the logical argument which checked to see if it was greater than 0!

Anyone?
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi,

Text is actually stored/represented by numbers within Excel, that's why there are functions you can use to include/exclude certain values, see examples below:
Someone else here may be able to explain this to you in more detail.


Excel 2010
ABCD
1textTRUEFALSETRUE
2123TRUETRUEFALSE
Sheet1
Cell Formulas
RangeFormula
B1=A1>0
B2=A2>0
C1=ISNUMBER(A1)
C2=ISNUMBER(A2)
D1=ISTEXT(A1)
D2=ISTEXT(A2)
 
Last edited:
Upvote 0
Whether I convert a number to "text" format, or use the apostrophe to indicate a text string that consists of numbers, I can still perform calculations on those strings. Why is that?
Changing the formatting doesn't change the content; you could just as easily change the format to a date and still evaluate it as a number.
Even crazier, I changed the number converted to "text" format to a string of letters, and my IF function still executed the logical argument which checked to see if it was greater than 0!
You seem to be working from the false premise that IF tests can't evaluate strings - or at least numbers formatted as strings. Suppose you input the formula:
=IF(A1<>0,1,0)
into B1. If A1 is empty, the result in B1 will be 0; if A1 contains any text or non-zero number, the result will be 1.
 
Upvote 0
Whether I convert a number to "text" format, or use the apostrophe to indicate a text string that consists of numbers, I can still perform calculations on those strings. Why is that?

Even crazier, I changed the number converted to "text" format to a string of letters, and my IF function still executed the logical argument which checked to see if it was greater than 0!

Anyone?

A text value is always greater than a number:

Let A1 house '1.

In B1 enter:

=A1>10

This should give TRUE as designed.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top