+ Reply to Thread
Results 1 to 16 of 16

Excel 2007 : How to sort text in column when the targeted words are not in order?

  1. #1
    Registered User
    Join Date
    04-15-2011
    Location
    Sofia
    MS-Off Ver
    Excel 2007
    Posts
    67

    How to sort text in column when the targeted words are not in order?

    I am using this way to sort text in a column - Conditional formating>Highlight cells rules>Text that contains... but when i try to sort text that contains two or more words, which are not in order i have a problem because the sort function doesn't highlight the text in the cells which is not in the specified order. For example, if i want to highlight all cells that contain the words "real music" it won't highlight the cells that have some words between these words. Like this words "real rock music", "real classic music" - it won't highlight them.

    Is there any way to highlight the cells that contain the words "real music" but they are in different order?


    Thanks!
    Last edited by emil9216; 04-16-2011 at 06:16 PM.

  2. #2
    Registered User
    Join Date
    03-15-2011
    Location
    cluj napoca
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to sort text in column when the targeted words are not in order?

    Hi
    select
    use a formula to determine which cell to format
    =NOT(AND(ISERR(SEARCH("real",G1)),ISERR(SEARCH("music",G1))))
    applies to G:G
    .

  3. #3
    Registered User
    Join Date
    04-15-2011
    Location
    Sofia
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: How to sort text in column when the targeted words are not in order?

    Thanks! I tried this and it works but is there any faster way I can do this because I have to work with a lot of text information, and it will save me a lot of time if I can sort the text without typing a formula?

  4. #4
    Registered User
    Join Date
    03-15-2011
    Location
    cluj napoca
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to sort text in column when the targeted words are not in order?

    if you write the text in a column then change the formula to:
    =NOT(AND(ISERR(SEARCH(I6:I7,G1))))
    I6= music
    I7= real
    I8=...

  5. #5
    Registered User
    Join Date
    04-15-2011
    Location
    Sofia
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: How to sort text in column when the targeted words are not in order?

    Can you be more detailed with this. I don't have to much experience with excel

    Let say the text i want to sort is in column G. I have to type the formula =NOT(AND(ISERR(SEARCH(I6:I7,G1)))) in the type bar but i am not sure how to use this I6= music, I7= real


    Thanks!

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: How to sort text in column when the targeted words are not in order?

    It appears meslija is offline so I'll try to explain his/her suggestion.

    The suggestion is to type your search criteria in cells I6 and I7 so that the formula doesn't have to be retyped in order to search for other criteria. You just change what you have in those 2 cells and the formulas automatically search for the new criteria.

    But the formula should use absolute referencing for those 2 cells so:

    =NOT(AND(ISERR(SEARCH(I$6:I$7,G1))))

    Does that clear up what the suggestion was?

  7. #7
    Registered User
    Join Date
    04-15-2011
    Location
    Sofia
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: How to sort text in column when the targeted words are not in order?

    I take a screenshot. Is that i have to do because it doesn't seem to work.

    \1

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: How to sort text in column when the targeted words are not in order?

    The discussion has been about Conditional Formatting so the formula that meslija was intended to be used in CF, not in a cell.

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to sort text in column when the targeted words are not in order?

    Try this

    Select a combination from the drop-downs in D1 & E1

    Formula is:=
    Please Login or Register  to view this content.
    Applies to:=
    Please Login or Register  to view this content.
    Format.......
    your choice (yellow)

    Is this what you mean?
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  10. #10
    Registered User
    Join Date
    04-15-2011
    Location
    Sofia
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: How to sort text in column when the targeted words are not in order?

    Quote Originally Posted by Cutter View Post
    The discussion has been about Conditional Formatting so the formula that meslija was intended to be used in CF, not in a cell.
    I am little confused. I thought i have to type the formula in the typing bar and don't have to use the conditional formatting. If i use the formula in the conditional formatting it gave me a error.

  11. #11
    Registered User
    Join Date
    04-15-2011
    Location
    Sofia
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: How to sort text in column when the targeted words are not in order?

    Quote Originally Posted by Marcol View Post
    Try this

    Select a combination from the drop-downs in D1 & E1

    Formula is:=
    Please Login or Register  to view this content.
    Applies to:=
    Please Login or Register  to view this content.
    Format.......
    your choice (yellow)

    Is this what you mean?



    Yes, this is what i am looking for but can you explain it with a little more details, please.


    Thanks!

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to sort text in column when the targeted words are not in order?

    Have a look as this demo workbook.

    The formula is broken down to its' components.
    =SEARCH($D$1,G1,1) will return #VALUE if the word is not in the string and a number if it is.
    So we use
    =ISNUMBER(SEARCH($D$1,G1,1)) to return TRUE/FALSE

    TIP
    Don't quote whole posts in your replies, the moderators don't like it.....

    Only quote posts if they are relevant to some query, and then only quote the relevant part of the post.
    Attached Files Attached Files
    Last edited by Marcol; 04-16-2011 at 02:01 PM. Reason: Added TIP

  13. #13
    Registered User
    Join Date
    04-15-2011
    Location
    Sofia
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: How to sort text in column when the targeted words are not in order?

    Quote Originally Posted by Cutter View Post

    But the formula should use absolute referencing for those 2 cells so:

    =NOT(AND(ISERR(SEARCH(I$6:I$7,G1))))

    Does that clear up what the suggestion was?


    Is this what i have to do? I have to choose Conditional formatting>New rule>Use a formula to determine which cells to format.... Then i have to copy/paste the formula =NOT(AND(ISERR(SEARCH(I$6:I$7,G1)))) and click "format". Then choose a color and click "OK".

    Is this the right way?

  14. #14
    Registered User
    Join Date
    03-15-2011
    Location
    cluj napoca
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to sort text in column when the targeted words are not in order?

    hello,
    yes but after that you have to chose where to apply. see the attachment.
    Attached Images Attached Images

  15. #15
    Registered User
    Join Date
    04-15-2011
    Location
    Sofia
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: How to sort text in column when the targeted words are not in order?

    Thanks! I think I know how to do it now.

    I have a similar problem with highlighting duplicated text in two columns, but I will open a new thread for this.

  16. #16
    Registered User
    Join Date
    04-15-2011
    Location
    Sofia
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: How to sort text in column when the targeted words are not in order?

    One more question. How will look this formula if i want to use it in a cell and not with conditional formatting? And how can i save this formula?


    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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