I have an Excel file I'm working with. There is a column that contains numbers and text, sometimes it's just one or the other. I'm trying to write a function that scans the left most part of the cell to see if it starts with a number. I thought I had it but apparently not. This is what I had:
=IF(ISNUMBER(LEFT(E8,1)), "True", "False")
This continues to throw me a "false" result even though that particular cell, E8, begins with a "3". What am I missing here?
4 Answers
Try this out:
=IF(ISNUMBER(VALUE(LEFT(E8,1))),"True","False")
Using the IF statement is redundant. The most simple and effective way to achieve your desired result is:
=ISNUMBER(--LEFT(E8,1))
It will automatically return TRUE or FALSE
Note that you can achieve what you require with just the following:
=NOT(ISERROR(LEFT(E8)*1))
If you do not LEFT(E8) evaluates to LEFT(E8,1) and multiplication by 1 throws an error on anything non-numeric
If you need your output as strings then update as per below:
IF(NOT(ISERROR(LEFT(E8)*1)),"True","False")
EDIT
Using ISNUMBER() is a good alternative to NOT(ISERROR())
=ISNUMBER(LEFT(A1)*1)
=IF(ISNUMBER(LEFT(A1)*1),"True","False")
=IFERROR(IF(VALUE(LEFT(E8,1)),"TRUE","FALSE"),"FALSE")
