Results 1 to 5 of 5

Thread: Find Color in the string using formula.

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14

    Find Color in the string using formula.

    Hi all,

    I have some string in a column e.g:-

    The pen is blue.
    Red eyes of devil.
    Sky is blue.
    White is my favorite color.


    and in other column i have some color listed e.g:-

    Red
    Green
    Blue
    Black
    Brown
    White
    Pink

    now i want the color in the string based on the color listed in other column e.g:-

    The pen is blue. blue
    Red eyes of devil. Red
    Sky is blue. Blue
    White is my favorite color. White


    only by using formula.

    thanks in advance

  2. #2
    Junior Member NBVC's Avatar
    Join Date
    Jun 2012
    Posts
    5
    Rep Power
    0
    Assuming sentences are starting in A2 and your colour table is in F2:F8, then try:

    =LOOKUP(9.9999E+307,SEARCH($F$2:$F$8,A2),$F$2:$F$8 )

    copied down

  3. #3
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Thanks NBVC...

  4. #4
    Junior Member
    Join Date
    Feb 2012
    Posts
    1
    Rep Power
    0

    =LOOKUP(9.9999E+307,SEARCH($F$2:$F$8,A2),$F$2:$F$8 )

    Can you explain what the 9.9999E+307 represents?

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Color Functions In Excel
    By Admin in forum Download Center
    Replies: 2
    Last Post: 10-24-2013, 11:44 AM
  2. Sort Data Using Formula To Find Top X
    By mahmoud-lee in forum Excel Help
    Replies: 12
    Last Post: 06-02-2013, 10:13 PM
  3. Replies: 4
    Last Post: 04-05-2013, 12:08 PM
  4. Find the First or Last So Many Words in a Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 6
    Last Post: 06-21-2012, 09:42 PM
  5. AutoFilter by Multi Color
    By Admin in forum Download Center
    Replies: 0
    Last Post: 09-08-2011, 07:17 AM

Tags for this Thread

Posting Permissions

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