# compare first 2 letters of cell and clear if not there

##### Board Regular
Hello All,

Here's one for you!!!

I have information in rows b2:b3567

and i have information in rows d2:d375

I would like to compare the first 3 letters of each line of data in d2:d375 against the first 3 letters in b2:b3567 and clear the contents of any within b2:b3567 if the first 3 letters do not match the first 3 letters in d2:d375.

if that makes sense !?!?

Cord.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Lewiy

##### Well-known Member
Do you mean to check the first 3 letters of each cell in B against every cell in D and clear it if there is no match at all?

##### Board Regular
Lewiy!!!

How are you?

yes you're right compare every cell but only clear the data in cells b2:b3567 if there is no match within the first 3 letters against d2:d375

#### Lewiy

##### Well-known Member
Ok, try this:
Code:
``````Sub MyMacro()
Application.ScreenUpdating = False
Dim b As Long
Dim d As Long
Dim flag
For b = 2 To 3567
flag = False
For d = 2 To 375
If Left(Cells(b, 2), 3) = Left(Cells(d, 4), 3) Then
flag = True
End If
Next d
If flag = True Then
Cells(b, 2).ClearContents
End If
Next b
Application.ScreenUpdating = True
End Sub``````

##### Board Regular
that worked to remove the ones which did match, so i changed your flag = true statement to flag = false and hey presto !!!

thanks again lewiy, you trully are a credit to this forum!!!

Replies
26
Views
2K
Replies
13
Views
1K
Replies
9
Views
512
Replies
5
Views
452
Replies
8
Views
315

1,190,587
Messages
5,981,805
Members
439,735
Latest member
Cbegg

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