Extracting text from a cell

  • Thread starter Thread starter James
  • Start date Start date
J

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
 
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.
 
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
 
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.
 
Back
Top