Forum Discussion
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-BDR...
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 *)
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 *)
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.
