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

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

Thanks!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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

--
Regards,
Tom Ogilvy

dukejas wrote in message
...

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

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

Thanks!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
How to get rid of string which contain a substring like 'PO BOX'.

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

Thanks!