remove salutations

[rakesh seebaruth]

Dear Guys

In Column A from A1:A500, I have e.g. Mr John Smith and Mr Mick Steeve

I need a formula or vba that would remove Mr and Mr and paste it in cell B

Thanks in advance.

regards

rakesh

 

[rakesh seebaruth]

Re: remove saluations

thanks it works. You are a genius. thanks again

 

[Peter_SSs]

Re: remove saluations

thanks it works. You are a genius. thanks again

Glad you got a successful outcome. Thanks for letting us know.

 

[rakesh seebaruth]

Re: remove saluations

can you translate this to vba for me please

 

[mandukes]

Re: remove saluations

Hi , I modified my original code.

Try this..

Sub Sal()

Dim mStr  As String
Dim lastrow As Long
Dim arr 'As String
lastrow = Cells(Rows.count, "A").End(xlUp).Row
For i = 1 To lastrow
mStr = ActiveSheet.Range("A" & i)
arr = Array("Mr", "Mrs", "Dr", "Prof")
 c = 0
For j = 1 To Len(mStr)
 sStr = Mid(mStr, j, 2)
 For k = 0 To 3
 If sStr = arr(k) Then
 c = c + 1
 If c > 1 Then sal = sal & " AND "
  sal = sal & sStr
End If
  Next k
Next j
 ActiveSheet.Range("B" & i) = sal
sal = ""
Next i

end sub

PS: Don't forget to hit the thumbs up if you like the solution

 

[mandukes]

Re: remove saluations

Try this...

'Modified


Dim mStr  As String
Dim lastrow As Long
Dim arr 'As String
lastrow = Cells(Rows.count, "A").End(xlUp).Row
For i = 1 To lastrow
mStr = ActiveSheet.Range("A" & i)
arr = Array("Mr", "Mrs", "Dr")
 c = 0
For j = 1 To Len(mStr)
 sStr = Mid(mStr, j, 2)
  For k = 0 To 2
 If sStr = arr(k) Then
 c = c + 1
   For l = 0 To 2
sStr = Mid(mStr, j, 3)
 If sStr = arr(k) Then
 c = c + 1
 End If
Next l
 If c > 1 Then sal = sal & " AND "
  sal = sal & sStr
End If
  Next k
Next j
 ActiveSheet.Range("B" & i) = sal
sal = ""
Next i

 

[Peter_SSs]

Re: remove saluations

can you translate this to vba for me please

If you have a preferred or required method, best to state that up front so the job doesn't have to be done twice.

Try

Sub Salutations()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("if(#="""","""",trim(#))", "#", .Offset(, -1).Address))
    .Value = Evaluate(Replace(Replace("trim(mid(substitute(#,"" "",rept("" "",20)),20,20))&if(isnumber(find(^,#)),^&mid(#,find(^,#)+5,find("" "",#,find(^,#)+5)-find(^,#)-5),"""")", "#", .Address), "^", """ AND """))
  End With
End Sub

 

[rakesh seebaruth]

Re: remove saluations

thanks peter