selecting letters from a word

[clansman]

Hi,

I need help to pull letters from a word and compare them with another group of letters from another word....

eg

B3: AB-CDE-1234
D3: FH-IJKL-1234

I need to extract the letters "CDE" in cell B3 and extract "JKL" from cell D3. I then need to compare both with each other and mark the cell (B3 and D3) green if the same or red if different

It will all ways be the 4rd, 5th and 6th character in B3
and 5th, 6th and 7th in D3

if possible i need this to repeat down the columns B4 with D4, B5 with D5 etc..

Thanks in advances

 

[T. Valko]

Hi,

I need help to pull letters from a word and compare them with another group of letters from another word....

eg

B3: AB-CDE-1234
D3: FH-IJKL-1234

I need to extract the letters "CDE" in cell B3 and extract "JKL" from cell D3. I then need to compare both with each other and mark the cell (B3 and D3) green if the same or red if different

It will all ways be the 4rd, 5th and 6th character in B3
and 5th, 6th and 7th in D3

if possible i need this to repeat down the columns B4 with D4, B5 with D5 etc..

Thanks in advances

What version of Excel are you using?

 

[clansman]

sorry forgot to say that. Its 2003.

 

[T. Valko]

sorry forgot to say that. Its 2003.

Ok, you can use conditional formatting to do this.

Let's assume the ranges are B3:B10 and D3:D10.

Select the *entire* range B3:B10 starting from cell B3.
Cell B3 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the menu Format>Conditional Formatting
Condition 1
Select the Formula Is option
Enter this formula in the box on the right:
=MID(B3,4,3)=MID(D3,5,3)
Click the Format button
Select the Patterns tab
Select a shade of GREEN
OK
Click the Add button
Condition 2
Select the Formula Is option
Enter this formula in the box on the right:
=MID(B3,4,3)<>MID(D3,5,3)
Click the Format button
Select the Patterns tab
Select a shade of RED
OK out

Repeat the process for the range D3:D10. The formulas will be the same.

 

[clansman]

Thank you T. Valko for the quick reply

however if a value is changed in B or D eg B10 or D10, B9 will go red not B10,

can this be done as a macro because I have many excel files to run this operation on.

Thanks

 

[T. Valko]

Thank you T. Valko for the quick reply

You're welcome!

 

[clansman]

sorry for digging up an old thread but this be done as a macro because I have many excel files to run this operation on.

thanks

 

[T. Valko]

sorry for digging up an old thread but this be done as a macro because I have many excel files to run this operation on.

thanks

Probably, but I'm not much of a programmer.

Try starting a new thread and let them know you want a VBA solution.

Good luck!