1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Tricky Excel formula needed

Discussion in 'Business Applications' started by slurpee55, Sep 30, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. slurpee55

    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)
     
  2. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    62,942
    First Name:
    Chuck
    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.
     

    Attached Files:

  3. slurpee55

    slurpee55 Thread Starter

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Excellent!!! Thanks, Chuck! :)
     
  4. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    62,942
    First Name:
    Chuck
    You are very welcome. (y)

    Glad to help out.
     
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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/754932

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice