+ Reply to Thread
Results 1 to 11 of 11

recognise words in multiple cells with a unique output

  1. #1
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    recognise words in multiple cells with a unique output

    Hello,

    in column A i have fruit words (e.g. apple, banana, orange...)
    in column B i have cities (e.g. london, paris, rome...)

    i would like a formula in column C that gives "british apples" when "apple" and "london" are on the same row
    whilst also giving "french bananas" when "paris" and "banana" are on the same row.


    thank you

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: recognise words in multiple cells with a unique output

    Assuming you have a table that lists which country each city is in:

    C1 = INDEX(country_range, MATCH(B1, city_range, 0)) & " " & A1 & "s"

  3. #3
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: recognise words in multiple cells with a unique output

    its not a table.
    just a column of words

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: recognise words in multiple cells with a unique output

    If you don't have some data linking cities to countries then it's impossible.

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: recognise words in multiple cells with a unique output

    Hi,

    Are the two conditions you give the only ones to check for? Or are there many more?

    Regards
    Click * below if this answer helped

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

  6. #6
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: recognise words in multiple cells with a unique output

    im pretty sure its not impossible.
    in column A there are fruit words.
    in column B there are cities.
    cell C1 checks if A1 and B1 contain either "apple" and "london" or "banana" and "paris" respectively. if there is a match then "british apples" or "french bananas" is written in cell C1.

  7. #7
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: recognise words in multiple cells with a unique output

    i may add more in the future but im sure that wont be too difficult. ill just extend the formula to include any additional conditions.

  8. #8
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: recognise words in multiple cells with a unique output

    How is anything supposed to turn "london" into "british" or "paris" into "french" without a table listing which country each city is in???

    That's the part that's manifestly impossible; once you've got that, it's a dead-simple text concatenation.



    EDIT:
    Wait if it's for those-two-and-those-two-only combinations it would just be something like this:

    Please Login or Register  to view this content.
    Or do you want, like, "italian oranges" when it's "rome" and "orange"? I assumed so but now I'm not sure.
    Last edited by ben_hensel; 05-09-2014 at 10:46 AM.

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: recognise words in multiple cells with a unique output

    Quote Originally Posted by ben_hensel View Post

    C1 = INDEX(country_range, MATCH(B1, city_range, 0)) & " " & A1 & "s"
    Cherry --
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  10. #10
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: recognise words in multiple cells with a unique output

    thats is!!!
    i guess a little miscommunication. it was always going to be for those 2 combinations.
    i knew it was fairly simple.

    thanks ben

    Quote Originally Posted by ben_hensel View Post
    How is anything supposed to turn "london" into "british" or "paris" into "french" without a table listing which country each city is in???

    That's the part that's manifestly impossible; once you've got that, it's a dead-simple text concatenation.

    EDIT:
    Wait if it's for those-two-and-those-two-only combinations it would just be something like this:

    Please Login or Register  to view this content.

  11. #11
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: recognise words in multiple cells with a unique output

    >>cherry

    Ha ha, yeah

    We could, like, check if the last letter is "y" and then clip it with LEN and slap an "ies" on the end instead of just "s" if we needed to, but I wanted to keep it simple to demonstrate the points I wanted to make, and only add complexity if it was needed.

+ 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. Formula to recognise one word from multiple words in the same cell
    By JLucy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-27-2013, 11:48 AM
  2. Recognise and count specific words
    By BBoyAl in forum Excel General
    Replies: 2
    Last Post: 03-23-2012, 05:15 PM
  3. COUNTIF for multiple unique words in a range.
    By lallo.jr in forum Excel General
    Replies: 3
    Last Post: 02-24-2012, 03:05 PM
  4. formula output to multiple cells
    By Sinamas in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-02-2010, 10:30 PM
  5. Genarating count of unique words in a cell or cells
    By Hari in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 01-28-2005, 03:06 AM

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