How Delete Text Based On Keyword List

[Kenshin]

Bow to the Ninjas
PLease desperate need help using formula only to solving this

thanks in advanced

 

[Logit]

Option Explicit

Sub DeleteRowWithContents()

 Dim ExcludWords() As Variant
 Dim r As Long
 Dim txt As Range
 Dim ws As Worksheet
 Dim i As Long
 Dim LR As Long

  ExcludWords = Array("Ph.D.", "MBA.", "M.Sc.", "dr.", ",") '<-- add more terms here

Set ws = Sheets(1)

LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

For i = 2 To LR
Set txt = ws.Cells(i, "A") '
For r = 0 To UBound(ExcludWords)
   txt.Formula = Replace(txt.Formula, ExcludWords(r), "")
    Next r
Next i

End Sub

 

[Belleke]

I should add these extra lines of code to delete the spaces.
txt.Value = LTrim(txt.Value)
txt.Value = RTrim(txt.Value)

For r = 0 To UBound(ExcludWords)
  txt.Formula = Replace(txt.Formula, ExcludWords(r), "")
  txt.Value = LTrim(txt.Value)
  txt.Value = RTrim(txt.Value)
  Next r
Next i

 

[Peter Bartholomew]

@Kenshin
Since you requested a formula you may wish to consider the attached.
It searches for the start of the first of the post-nominal strings using the named formula, 'postnominal.start':
= MIN( IFERROR( FIND(postnominal.list, Names[@Full] ), "" ) )
and, similarly, the end of the last title, 'title.end':
= MAX( IFERROR( FIND( title.list, Names[@Full] ) + LEN( title.list ), "" ) )

The name itself is extracted using MID:
= MID( [@Full], title.end + 1, postnominal.start - title.end - 3 )

 

[Kenshin]

Thanks Logit, Belleke and Peter for amazing solutions, appreciate, thank you all

 

[Peter Bartholomew]

Some improvements to the formulae to make them a little less fragile:

= MAX( IFERROR( FIND( title.list & " ", Names[@Full] ) + LEN( title.list ), "" ) )
= MIN( IFERROR( FIND(" " & postnominal.list, Names[@Full] ),
LEN(Names[@Full])+2 ) )

The single space characters are intended to reduce the chance of accidental matches [Drew and Dr for example] and the length of the entire string is used to allow for situations in which there are no post-nominal letters. To accommodate the additional character in the title string, the worksheet formula is changed to

= MID( [@Full], title.end + 1, postnominal.start - title.end - 2 )

p.s. I did also define a named constant '_' to refer to = " ". I decided that my style of spreadsheet programming probably causes enough confusion without further embellishment!

 

[Logit]

You are welcome.