# CF using MID formula

#### Spraggs

##### Well-known Member
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-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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### prajul89

##### Active Member
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

#### Spraggs

##### Well-known Member
The value to be matched is any of the other 4998 resuts.

#### prajul89

##### Active Member
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.

##### MrExcel MVP

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

#### Spraggs

##### Well-known Member
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)

##### MrExcel MVP

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
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.

##### Well-known Member
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

##### MrExcel MVP
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.

Replies
10
Views
888

1,126,961
Messages
5,621,851
Members
415,862
Latest member
nascaline

### 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.

### Which adblocker are you using?

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

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