How to get rid of string which contain a substring like 'PO BOX'.

[dukejas]

How to get rid of string which contain a substring like 'PO BOX'.

Is that 'Like', it doesn't work.

Thanks!

 

[Tom Ogilvy]

Dim sStr as String
sStr = " the address contains a po box reference"

if instr(1, sStr, "po box", vbTextCompare) then
' it contains the substring "po box"


End if

 

[pfsardella]

Sub NoPO()
Dim rngP As Range

For Each rngP In Selection
If InStr(1, rngP.Value, "PO BOX", vbTextCompare) Then _
rngP.ClearContents
Next rngP

End Sub

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul