» Make LEFT Function recognize a number
How to force LEFT function to recognize a number.
The LEFT Function (as well as MID and RIGHT) treats number (digits) as Text, even
when contained in a true Number entry. Therefore, when using digits as operators within these functions, the output must be treated as Text.
Here are three different options that all return the desired result (recognizing the digit in the desired format:
=IF(LEFT(A1,1)=1,"Ignore",A1) [sees 1 as a number]
=IF(LEFT(A1,1)+0=1,"Ignore",A1) [sees 1 as a number]
=IF(LEFT(A1,1)="1","Ignore",A1) [sees 1 as text]
The example below shows the various formulas in Col 'C' and the result in Col 'B'
The desired result is "Ignore".
Note in all rows except row 6, the result of the LEFT function is Left aligned, as is default for Text.
The formula in cell B6 returns the cell A6 value (a true number) as the IF statement fails to find 1 as a digit (it only sees 1 as text), look also at formula in row 9.
|Screenshot // Make LEFT Function recognize a number|