+ 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 Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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 Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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 Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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)

Similar Threads

  1. [SOLVED] Find exact text in exact cell but on any sheet?
    By nobodyukno in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2017, 02:59 PM
  2. Macro to find a text and paste cell data in differnt worksheet.
    By richhhh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2016, 01:01 PM
  3. [SOLVED] looking to find 1 of 2 words in a cell in column B and return the word found in the same
    By Rob69mfj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-27-2014, 01:17 PM
  4. Find Words from an entire column in a cell
    By sra233 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2013, 11:41 PM
  5. Find Words from an entire column in a cell
    By sra233 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-31-2013, 04:16 PM
  6. [SOLVED] How to find the exact cell content in a column
    By yeshwant_sur in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2012, 02:13 AM
  7. Replies: 16
    Last Post: 03-09-2011, 12:27 PM

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