Formula question

[Ian]

Is it possible to clear the contents of a cell if if contains a certain
text strings, i.e. I want to clear all cells in a particular column that
contain the phrases "son of", "dau of" and "wife of".

Thanks in advance.

 

[Bernard Liengme]

How about Find & Replace?

 

[Ian]

How about Find & Replace?

No, that won't work because it will only replace part of the cell
contents, i.e. "son of William" would become "William" rather than
blank.

 

[JE McGimpsey]

Well, no. It will work.

Find: *son of*
Replace: <blank>

 

[Ron Rosenfeld]

Is it possible to clear the contents of a cell if if contains a certain
text strings, i.e. I want to clear all cells in a particular column that
contain the phrases "son of", "dau of" and "wife of".

Thanks in advance.

You could use a VBA macro:

=========================
Sub ClearSpecial()
Dim i As Long, Count As Long
Dim c As Range
Dim Phrases As Variant

Phrases = Array("son of", "dau of", "wife of")

For Each c In Selection
For i = 0 To UBound(Phrases)
If InStr(c.Text, Phrases(i)) > 0 Then
c.Clear
Count = Count + 1
End If
Next i
Next c

MsgBox (Str(Count) & " cells cleared")

End Sub
============================

To enter the macro, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the above code into the window that opens.

To use the macro, select the range containing the cells you wish to
conditionally clear. Then <alt-F8> opens the Macro Dialog box. Select your
macro and run it.

Many variations are possible depending on your precise requirements.


--ron

 

[Ian]

Well, no. It will work.

Find: *son of*
Replace: <blank>

I'm probably missing the point here but how will the Find/Replace
function clear the whole contents of the cell when I'm only searching
for a part of the whole string, ie how will searching for "son of"
replace "son of William" or "son of Henry James" etc ?

 

[CLR]

Highlight the column, then Edit > Replace > Find what" son of* Replace with:
leave empty > Replace all

Vaya con Dios,
Chuck, CABGx3

 

[RagDyeR]

You're *not* searching for
son of

You're searching for
*son of*

NOTE, John instructed you to include the wildcard "*".
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Well, no. It will work.

Find: *son of*
Replace: <blank>

I'm probably missing the point here but how will the Find/Replace
function clear the whole contents of the cell when I'm only searching
for a part of the whole string, ie how will searching for "son of"
replace "son of William" or "son of Henry James" etc ?

 

[Ian]

You're *not* searching for
son of

You're searching for
*son of*

NOTE, John instructed you to include the wildcard "*".

OK, sorry I overlooked that. Thanks for bringing my attention to it.

 

[Harlan Grove]

Well, no. It will work.

Find: *son of*
Replace: <blank>

....

Quibble: This would also clear out cells containing, e.g.,

Perry Mason often won his cases.

Substrings as part of longer words are a pain. Microsoft is a pain for not
making the enhanced wildcards available in Word available to Excel as well.

 

[Harlan Grove]

Highlight the column, then Edit > Replace > Find what" son of* Replace with:
leave empty > Replace all

....

If only there were spaces at the beginning. If not, major PITA.