Solved: Tricky Excel formula needed

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

slurpee55

Thread Starter
Joined
Oct 20, 2004
Messages
7,837
Ok, I'm having no luck.:(

I have a list of jobs (from an open-ended question) in a variety of formats - some like this:
job1/job2/job3/
or this
job1, job5, job2,
or this
job7/ I have job2, but am passing it on to person x, who has just started, job4
well, you get the idea, it is a mess.
The jobs are in no particular order, since the person could write in whatever they wanted.
Text to columns helps a bit, but now I have an idea that there are two jobs that I need to find listed among all of these - board secretary and human resources.
What I would like to do is find the people who have certain jobs somewhere in a line of text. Unfortunately, they can be in any order and of any length.

Can you help me write a formula that will find the board secretaries (probably will just look for "board", since some wrote board sect.) and another for the HR people?
Once I have one, I can probably adapt it to the other (and any future ones) I have.

Thanks gang!!! (y)
 

cwwozniak

Chuck
Trusted Advisor
Spam Fighter
Joined
Nov 28, 2005
Messages
66,820
Not sure if I fully understand what you have and what you want.

Does the attached file do what you want?

Column A has the names of the people taking the survey and the data is not used for the search.

Column B has the jobs listed by each person

You put whatever job name/description you wish to find in cell E2.

The formulas in column C use the SEARCH function to find the contents of E2 anywhere in column B for the given row. The search is not case sensitive but otherwise looks for an exact match. It returns the position of the first matching character if there is a match and returns an "#VALUE!" error if no match is found. The IF function returns an empty text string if no match is found and the word FOUND if a match is found.
 

Attachments

cwwozniak

Chuck
Trusted Advisor
Spam Fighter
Joined
Nov 28, 2005
Messages
66,820
You are very welcome. (y)

Glad to help out.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top