+ Reply to Thread
Results 1 to 8 of 8

How to use mutiple words in any order to display a cell

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    Delta, BC Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    How to use mutiple words in any order to display a cell

    I have been able to display a cell into another cell by using a single word from the first cell
    Eg.

    In Cell (KJV!$F2)
    " To know wisdom and instruction; to perceive the words of understanding;"

    =IF(ISERR(SEARCH(H$1,KJV!$F2)),"",KJV!$F2)
    Where H$1 is "wisdom" this will display properly

    I wanted to use multiple words and tried this Formula
    =IF(ISERR(SEARCH(H$1&"*"&I$1&"*"&J$1,KJV!$F2)),"",KJV!$F2)
    Where H$1 is "wisdom" I$1 is "instruction" J$1 is "understanding" this will display properly as well.

    But if I change the order H$1 is "instruction" I$1 is "wisdom" J$1 is "understanding"
    This will not display.

    What I am looking for is a formula that will allow me to input the words in any order and It will display.

    The other problem I discovered is if I use a word like "man" every word containing "man" will display the cell
    eg.
    many
    bibliomania
    caiman
    horseman

    I have tried using a space before and after only to discover that I lose all the results with punctuation. Like "man,"

    I hope this explanation is not to complicated.

    Thank you in advance for any suggestions.

    Eagleintl1

  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: How to use mutiple words in any order to display a cell

    Hi,

    It would be preferable to see a few more examples of the strings you're working on, but, for the timebeing, this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) will work for the example you give:

    =IF(SUM(--ISNUMBER(SEARCH(H1:J1,F2)))=COUNTA(H1:J1),F2,"")

    Regards
    Click * below if this answer helped

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

  3. #3
    Registered User
    Join Date
    07-30-2013
    Location
    Delta, BC Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to use mutiple words in any order to display a cell

    Thank you XOR LX for your reply

    I tried your formula but unfortunately it does not respond the way I hoped.
    If I put ant string of letters into H1 the result is displayed. I used "xyz' and it still displayed the line.

    Here are the cells F2 - F17
    To know wisdom and instruction; to perceive the words of understanding;
    To receive the instruction of wisdom, justice, and judgment, and equity; {equity: Heb. equities}
    To give subtilty to the simple, to the young man knowledge and discretion. {discretion: or, advisement}
    A wise man will hear, and will increase learning; and a man of understanding shall attain unto wise counsels:
    To understand a proverb, and the interpretation; the words of the wise, and their dark sayings. {the interpretation: or, an eloquent speech}
    The fear of the LORD is the beginning of knowledge: but fools despise wisdom and instruction. {the beginning: or, the principal part}
    My son, hear the instruction of thy father, and forsake not the law of thy mother:
    For they shall be an ornament of grace unto thy head, and chains about thy neck. {an…: Heb. an adding}
    My son, if sinners entice thee, consent thou not.
    If they say, Come with us, let us lay wait for blood, let us lurk privily for the innocent without cause:
    Let us swallow them up alive as the grave; and whole, as those that go down into the pit:
    We shall find all precious substance, we shall fill our houses with spoil:
    Cast in thy lot among us; let us all have one purse:
    My son, walk not thou in the way with them; refrain thy foot from their path:
    For their feet run to evil, and make haste to shed blood.
    Surely in vain the net is spread in the sight of any bird. {in the…: Heb. in the eyes of every thing that hath a wing}

  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: How to use mutiple words in any order to display a cell

    Thanks.

    Could you post this example where you did not get the desired results? - I'm curious as in my sheet typing "any string" (e.g. "xyx") in H1 certainly doesn't have that effect (see attached).

    Meanwhile, I'll have a look at the rest (and that issue of unwanted matches you mentioned).

    Regards
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-30-2013
    Location
    Delta, BC Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to use mutiple words in any order to display a cell

    I am posting my spreadsheet so you can see what I have done to date.
    You will notice I have "son" in H1 and it shows the result in F1. Even though son is not in the line.
    Ultimately I would like to place the words "son", "mother", "father" into H1, I1, J1 Not in nessesarly in that order
    and have the result in F11
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-30-2013
    Location
    Delta, BC Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to use mutiple words in any order to display a cell

    I have reviewed your file and have discovered it does work when referring to another page in the spreadsheet.
    I also would like the flexibility to use either 1, 2, or 3 words. with your formula all 3 fields have to be filled in order for it to work.

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

    Re: How to use mutiple words in any order to display a cell

    Hi,

    The reason it's not working is that you've decided, for whatever reason, to invert the TRUE and FALSE parts of the formula I originally posted.

    What's more, it does not appear that you heeded my note that this must be entered as an array formula.

    Regards

  8. #8
    Registered User
    Join Date
    07-30-2013
    Location
    Delta, BC Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to use mutiple words in any order to display a cell

    XOR LX

    Sorry for that.

    I am new to this and trying to figure it out. When it did not work I tried adjusting the formula and must have messed it up.

    I did how ever find out the real problem was once I inputted the formula I should have press Ctrl/Shift/Enter in order for it to woork

    I also discovered I can replace the cells with a space and that way I can use 1,2 or 3 words.

    I am not clear what an array formula is but I have a hunch it maybe the Ctrl/Shift/Enter thing.

    Again I am sorry for my ignorance I will try to be more attentive to your instructions next time.

    I thank you for all your help and am very happy right now.

+ 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. Great Forum
    By Eagleintl1 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-30-2013, 07:25 PM
  2. [SOLVED] How to choose words by criteria and display them in one cell?
    By bccb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2013, 05:36 AM
  3. [SOLVED] Formula to retrieve mutiple values less than value in specific cell, but in date order.
    By rocksan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2012, 11:33 PM
  4. Replies: 1
    Last Post: 04-04-2012, 07:15 PM
  5. Changing Series Order with Mutiple Chart Types
    By favoritepsalm1 in forum Excel General
    Replies: 1
    Last Post: 09-11-2008, 10:51 AM

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