Extracting text from a cell

[James]

Hi all,

I have a list of categories to indicate employee disciplines, i.e
Management
Electrical
Mechanical
Other
and so on.

I have other cells within my spreadsheet that have text that includes the
categories above. For example,

Electrical Engineer
Senior Electrical Engineer
Junior Electrical Engineer

I would like to search these text strings using the categories above to
return only the category. Therefore, if i had A1 = Electrical Engineer, B1
needs to be a formula that gives the result of Electrical. If I then changed
A1 to Mechanical Engineer, B1 needs to automatically change to Mechanical.

Any suggestions

 

[T. Valko]

Try this:

With your list of categories in the range J1:J4

Enter this formula in B1:

=LOOKUP(2,1/(ISNUMBER(SEARCH(J$1:J$4,A1))),J$1:J$4)

Copy down as needed.

Note that if a cell contains 2 or more categories: Other - Mechanical
Estimator, the formula will return the *last* category that matches. In this
case both Other and Mechanical are matches but Other appears in the category
list after Mechanical so the result of the formula is Other.

 

[James]

Thanks, that works great.

If I had an employee that was entered as Mechanical Director, how would I go
about classifying this guy as Management?

Also if an employee was entered as Building Simulation, how would I go about
classifying as Sustainability?


On a separate issue, I posted a question "Dividing hours between working
weeks". Have you got any thoughts on how I might tackle that?

Thanking you in advance

 

[T. Valko]

Ok, it could get a little tricky if you have lots of items that contain a
related "keyword" for another category. For example, Mechanical Director =
Management and Mechanical Engineer = Mechanical.

See this screencap:

http://img81.imageshack.us/img81/3900/lookupex9.jpg

You need to create a 2 column table. Notice in the table how I've listed
Mechanical and Mechanical Director. Mechanical director is listed *after*
Mechanical.

Or, for the entry Mechanical Director you could use Director as the keyword.
Then in the lookup table you would replace Mechanical Director = Management
with Director = Management.