We help IT Professionals succeed at work.

Excel VBA Evaluate cell text

Calvin LeBlanc
on
494 Views
Last Modified: 2012-02-17
Hi there experts, : )
I'm challenged with manually evaluating columns of 300 to 400 job titles by cell key words and providing codes in the cell to the right of the evaluated cell.  
Examples:
CEO CFO Chief Information Officer would result in: CFI
COO Chief Executive Officer would result in: OC
President would result in: C
Support Manager would result in: M
Vice President of Operations would result in: VO

I'm looking for code (hopefully in array fashion) to loop through each populated cell in column A and add the applicable code attributes to the cell in column B, then move to the next cell in the column and on to the end of the list. any suggestions?
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2012

Commented:
This seems like a straightforward Vlookup problem, with a table having the title in first column and code in the second.

If, in A2, we have the title, the B2 would be:

[B2]=VLOOKUP(A2,$E$4:$F$8,2,0)

and copy down.

see attached.

Do you have titles with more information so we have to find the title inside a larger set of words?  Is there a need to do this with VBA for some reason?

Dave
lookupTitleCodes-r1.xls
Calvin LeBlancReporting Engineer

Author

Commented:
Hi Dave,  its a little more complicated than a single lookup..  many of the titles have two or three titles within the string. I'm looking for a way to loop through the cell string and identify  each individual title within the cell string when there are more than one titles provided. in the example below, the code would run through the cell string and find COO and log an "O" into the cell to the right for operations, then loop through the string again and find Chief Executive Officer and log a "C" for CEO. the code would run through the cell string again and find nothing new then move on to the next cell below. (maybe at this point add an "x" for complete)

COO Chief Executive Officer would result in: OC

If someone can assist me with looping through a single cell string (possible using the select/case) until nothing new is found, then move to the cell below. I can build out the rest of what I need.

Thanks in advance.
Most Valuable Expert 2012
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2012
Top Expert 2012

Commented:
Please test the example before responding.  Feel free to put comments into the file to assist in finalizing the solution.  I will look at this first thing in the AM if you have any issues.

Dave
Calvin LeBlancReporting Engineer

Author

Commented:
Dave this is a very good solution and gives me something to work with.  thank you very much for your efforts. I do appreciate the assistance.
Calvin LeBlancReporting Engineer

Author

Commented:
Thank you sir !
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.