Results 1 to 3 of 3

Is This Possible?

This is a discussion on Is This Possible? within the Excel Questions forums, part of the Question Forums category; I had a problem yesterday and you all helped me solve it! Thanks much. Now another one. I don't know ...

  1. #1
    New Member
    Join Date
    Jul 2003

    Default Is This Possible?

    I had a problem yesterday and you all helped me solve it! Thanks much.

    Now another one. I don't know if this is possible or a really dumb question so bear with me.

    I have this HUGE spreadsheet. It has a column with information, such as:
    Wladyslaw Stanislaw Reymont, Poland, novelist
    Jan R Slotemaker de Bruane, Dutch clergyman/politician
    Joseph Cuvelier, Belgian historian/archivist
    Amedos Peter Giann San Jose, CA, founded Bank of America
    John McClutcheon, cartoonist
    August Reusner, composer
    Chi Morgenstern, writer

    The next column will state which Category each cell falls into:
    For example:
    History & Politics
    Literature & Theater
    Music, etc.

    Is it possible to write something that states "If cell has 'composer' in it, then type "Music" in following cell? And so forth? I know this will not work 100% because of the wording of some cells however I have probably 50,000 entries and would LOVE not to have to type each one by hand.

    Any suggestions?
    Thanks in advance,

  2. #2
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    39 17' 15" N, -94 40' 26" W

    Default Re: Is This Possible?

    You could use the Excel function Search() like, =If(IsError(Search(Upper(A1),"COMPOSER")),"","Music"). But that's gonna be a pain to do for multiple criteria/categories.

    I'd probably go with using Data | Filter | Autofilter and setting the criteria to Custom and using the "Contains" option. Then once autofilter has filtered for you, highlight the cells you want to fill, type in your value in the ActiveCell and hit Ctrl+Enter to fill all the cells simultaneously.


    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  3. #3
    Board Regular
    Join Date
    Apr 2004


    I might be tempted to do Data / Text to Columns / Delimited and select comma as the delimiter. Then select your data and sort based on the occupation. This will at least group the entries by occupation, and will help you out with variances in spelling etc.

    - Kevin

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts