+ Reply to Thread
Results 1 to 6 of 6

Find words (not exact value) in a cell and populate a cell in a differnt column

  1. #1
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    Excel 2016
    Posts
    3

    Find words (not exact value) in a cell and populate a cell in a differnt column

    I have these 2 columns
    house in washington House purchases
    house in detroit House purchases
    purchased car blue
    car purchased in delaware Car purchases
    iowa house in House purchases

    Every time that Excel finds in column A the words "house in", I want to populate column B with the words "House in city" (which refers to the category). Similarly, if Excel finds in column A the words "car purchased "or "purchased car", I want to populate column B with the category "Car purchases". I tried using IF statement, but it's not working. The thing is that I am not looking for an exact value. If the words "house in" are in the cell, it doesn't matter if there is a 3rd word describing the city or the state, it should be classified in the ''House purchases'' category. I need to finish this by tomorrow. Any quick insights you can provide would be very much appreciated. Thank you again.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,665

    Re: Find words (not exact value) in a cell and populate a cell in a differnt column

    Hi,

    =LOOKUP(1,0/SEARCH({"car purchased","house in","purchased car"},A1),{"Car","House","Car"}&" purchases")

    P.S. You don't say what the return should be if none of those phrases are found within the string, so I didn't add anything to that effect.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: Find words (not exact value) in a cell and populate a cell in a differnt column

    XOR LX Thank you so much. Yes, you're right. How do I add the return when none of the phrases are found? In addition, can you please share a resource so I can understand how you came to this solution? With continued thanks.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,665

    Re: Find words (not exact value) in a cell and populate a cell in a differnt column

    What do you want the formula to return in such cases? The original cell value?

    I'll post an explanation after updating the formula as you require.

    Cheers

  5. #5
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: Find words (not exact value) in a cell and populate a cell in a differnt column

    I would like the formula to return the category "Other". But I wouldn't also mind learning how to return the original cell value. Thanks.

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,665

    Re: Find words (not exact value) in a cell and populate a cell in a differnt column

    Ok, try:

    =LOOKUP(1,0/SEARCH({"","car purchased","house in","purchased car"},A1),{"Other","Car","House","Car"}&" purchases")

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1