Conditional formatting in a text string

[radagar]

Hi all,

I have a question and i appreciate your time.

In excel want to use a formula to find a text string duplicate in another cell and have that cell's background change to a different color. Simple so far the second cell with have the item i am looking for plus additional text. I will give an example.

A1 - a12345 ||B1 - A12345
A2 - a12345 my name is john ||B2 - a112233
A3 - A12343 my name is smith ||B3 - a111111
A4 - A12344 ||B4 - b333344

So in this example I want to be able to have A1,A2, And B1 change to a blue background

thank you for your time i am still new at excel.

[Ron Coderre]

• Select A1:B4
• Home.Conditional_Formatting...New Rule...Use a formula to determine which cells to format
Formula: =SEARCH("a12345",A1)
...Click: Format
...Set the fill color
...Click: OK...Click: OK

Note: Do not use dollar signs in the cell reference


Is that something you can work with?

[radagar]

you sir are a gentleman and a scholar.

Not only were you quick and very through you solved my issue in one simple answer.

thank you you are the best!

[Ron Coderre]

Glad you got something you can use! and thanks for the kind words

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)

[radagar]

I should have clerified i need to find a item i copy and paste from a list in column B with a list i coppy and paste in column a and have them turn a diffrent color. This works very well for exact text data but how do i just serch for the value of the cell it see if it matches the value of a list of cells.

i want to find B1 and i want to look for a match in A1 to A2000 (and the a column will have the item im looking for and text) so B1 to B2000 will be the unknown values i want to find and if found make the cell brown.

[Ron Coderre]

The formula would be:

Please Login or Register  to view this content.

The way conditional formulas work is...
Zero = FALSE
All other numeric values = TRUE

So, you'd select B1:B2000 (with B1 as the active cell
and set the conditional format formula (above).

Does that help?

[radagar]

The help is appreciated. I need it to find a alpha numeric string that's random in cell b1 to b2000 and see if it is a duplicate of cell A1 to A2000 that will have the same random alpha numeric string with random text in the same cell. So b1 may be a12345 and A1 may need to be "a12345 this is extra text" or it may be anywhere on column A I need to find the duplicate matchs

[Ron Coderre]

Try this:

Please Login or Register  to view this content.

The asterisks are wildcards, indicating 0 or more characters

[radagar]

I think this may do the trick ty

[radagar]

That did it thank you again!