CF using MID formula

Spraggs

Well-known Member
Joined
Jan 19, 2007
Messages
704
Hello All (Using Excel 2007)
In column A I have list of cells that I want to CF. (A2:A5000)
Example of cell entries....

2468-WST-HEN-HTG-BI032
2468-GSV-BLD-ADM-EC016S
2468-GSV-BLD-EFC-EC052S
2468-GSV-QSJ-L01-BI032
2468-QSE-MAN-17B-EC506E
2468-QSJ-MAN-12F-EC760J
2468-QSH-FIN-26F-WS011
2468-WST-PCK-26D-CR024
2468-QSH-PCK-26A-FC144
2468-WHP-MAN-L02-SC047
2468-QSH-PCK-MBP-WP109
2468-QSH-FIN-26F-CR024

I want to conditional format if...... =MID(A2,18,7) is = to another cell up to cell A5000 and the same for any of the other cells in that range.

Thank You
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

prajul89

Active Member
Joined
Jul 9, 2011
Messages
404
Goto conditional formating>>New rules>>Use a formula to determine which cell to format

then add this formula in the bar =MID(A1,18,7)=F1 (assuming the value to be matched is in F column) and change the format as per requirement.
I hope this solves your problem
 

prajul89

Active Member
Joined
Jul 9, 2011
Messages
404
oh Its quite difficult, though I will try.
But it would be easy IF you put the mid formula else where in the sheet(say F column) then the formula in conditional formating would be =COUNTIF(F:F,MID(A1,18,7))>1.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

MID(A2,18,7) must be equal to what exactly?
 

Spraggs

Well-known Member
Joined
Jan 19, 2007
Messages
704
MID(A3,18,7)
MID(A4,18,7)
MID(A5,18,7)
MID(A6,18,7)
MID(A7,18,7)
etc......
up to A5000

i.e. =MID(A2,18,7) in cell B2
=COUNTIF($B$2:$B$5000,B2) in cell C2

But encompassed in cell A2 as a CF (CF if result is>1)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

MID(A3,18,7)
MID(A4,18,7)
MID(A5,18,7)
MID(A6,18,7)
MID(A7,18,7)
etc......
up to A5000

i.e. =MID(A2,18,7) in cell B2
=COUNTIF($B$2:$B$5000,B2) in cell C2

But encompassed in cell A2 as a CF (CF if result is>1)

A2: some string...

B2:

=MID(A2,18,7)

C2:

=COUNTIF($B$2:$B$5000,B2)

What cell do you want to conditionally format and what is the rule? is it

CF A2 when C2 > 1?
 

Spraggs

Well-known Member
Joined
Jan 19, 2007
Messages
704
I want to condition all the cells A2:A5000
The condition is if the last part of the cell (18th digit onwards) is equal to any of the other 4999 cells (18th digit onwards) then format to suit.
i.e. The following cells would meet criterea.

2468-WST-HEN-HTG-BI032
2468-GSV-QSJ-L01-BI032
Hence both contain BI032 from the 18th digit.

Sorry for poor explanation.
 

AdamL

Well-known Member
Joined
Sep 25, 2008
Messages
767
In A2 apply this conditional formatting:

=SUMPRODUCT(--(MID(A2,18,7)=MID(A$2:A$5000,18,7)))>1

and then copy this formatting down the column. Does that work?

HTH
Adam
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
I want to condition all the cells A2:A5000
The condition is if the last part of the cell (18th digit onwards) is equal to any of the other 4999 cells (18th digit onwards) then format to suit.
i.e. The following cells would meet criterea.

2468-WST-HEN-HTG-BI032
2468-GSV-QSJ-L01-BI032
Hence both contain BI032 from the 18th digit.

Sorry for poor explanation.

Select A2:A5000.
Fire up CF.
Choose Use a formula as Rule type.
Invoke the following formula in the white space:

=IF($A2<>"",COUNTIF($A$2:$A$500,"*"&RIGHT($A2,5))>1)

Activate the Format button.
Choose the formatting you want.
Click OK, OK.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,087
Messages
5,622,628
Members
415,915
Latest member
Eng Said Ebead

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top