New Contributor

# Change different numbers in a text string a certain color

I have data in excel that is a text string, example BDR103-BDR108-BDR328-BDR605, in a range of 15675 rows in one column.

 BDR103-BDR108-BDR328-BDR605 BDR108 BDR108 BDR108 BDR101-BDR108 BDR104-BDR108-BDR401 BDR103-BDR108 BDR104-BDR108 BDR108 BDR104-BDR604 BDR103-BDR108-BDR328-BDR605 BDR104 BDR104-BDR108-BDR300 BDR104-BDR108-BDR300 BDR108 BDR101-BDR108-BDR401-BDR603 BDR108

I have multiple rows with different numbers after BDR and I need to change BDR103 and BDR108 blue and BDR328 and BDR605 red.  I actually have 10 specific BDR numbers I need to turn blue if they appear in the text string and 12 BDR numbers I need to turn red if they appear in the text string.  Some of the BDR#'s will stay black.  Does anyone have any clue how to make this happen?  Thanks for your help!

2 Replies

# Re: Change different numbers in a text string a certain color

conditional formatting -> new rule -> custom formula:
assuming column A and it has to be the first part then you are looking at the first 6 characters or you can look at character 4-6 (both examples are here):
=(LEFT(A1,6)="BDR103") + (MID(A1,4,3)="108")
and set the formatting to Blue
if the string is anywhere in the cell then
=IFERROR(SEARCH("BDR328",A1),0) + IFERROR(SEARCH("BDR605",A1),0)
and set format to Red
a couple points to know is that A1 must be the upper left corner of the range that the conditional formatting is being applied to.
the + in the equations works like an OR (if you needed to have AND then you use *)

# Re: Change different numbers in a text string a certain color

Thank you, but I don't think a formula will work for what I'm trying. I think the only way to do it will be using visual basic.

I have a range, AR2:AR15676, and all cells are filled with random text strings. I need to search these text strings for BDR10, BDR12, BDR111, BDR112, BDR113, BDR307, BDR313, BDR325, BDR326, BDR403, BDR502, and BDR600 and highlight them red in the text string when found. I also need to search for BDR1, BDR13, BDR16, BDR100, BDR110, BDR301, BDR302, BDR303, BDR329, and BDR605 and highlight them blue in the text string when found.