New column based on other's text

[jcollier]

I have a table with two columns: employees and their status.

What I'd like to do:

I'd like to classify the status into two categories: active and inactive.

I know I should create new table only with status and its assigned category, then make a relationship.

Questions:

1. How can I automatically classify status starting with number as 'inactive' category?

2. How can I automatically classify status with text 'idle' as 'inactive' category?

Original table:

employee	status
John	idle
William	7861
Anna	training
Frances	cleaning
Jason	#foo

Calculated table:

status	category
idle	inactive
7861	inactive
training	active
cleaning	active
#foo	inactive

[Greg_Deckler · Accepted solution]

See response in other thread, catches everything except #foo, not sure of that one but if you can supply the rule, you can add it to the IF statement:

Column = IF(NOT(ISERROR(VALUE(LEFT([status],1)))) || [status] = "idle", "inactive", "active")