CODEWITHSUNDEEP
excelexcel-formulaexcel-2010worksheet-function
Joshua Burton
Joshua Burton

Reputation: 157

Determine whether a cell value's first character is numeric or text?

I am trying to find a good way to use Excel to determine whether a cell's first character is a number or a letter. I am using this to determine a flag that gets marked or not depending on the answer. Here is an example table:

**Status    Code**
Inactive    2AJ
Active      ALO
Active      PN9
Active      Y2Y
Inactive    1P9

Essentially, if the beginning character of the Code column is a numeric value, the Status column should show "Inactive". Currently I have this and it doesn't work:

=IF(ISNUMBER(LEFT(B1,1)),"Inactive","Active")

Upvotes: 5

Views: 48844

Answers (2)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

Formula:

=IF(ISNUMBER(VALUE(LEFT(B2,1))),"Inactive","Active")

You were trying to see if a number stored as a string was a number. This fixes that issue.

Upvotes: 16

pnuts
pnuts

Reputation: 59485

Please try: 

=IF(AND(CODE(LEFT(B2,1))>47,CODE(LEFT(B2,1))<58),"Inactive","Active")

copied down to suit.

This might then be compared against the ColumnA values to flag any discrepancies.

Upvotes: 1

Related Questions