Replacing unknown values/text in the same cell as a known word

[JaspeR_Etzi]

Hey you all,
I am pretty much a newbie to the whole Excel macro area and I tried my best to Google the answer for my problem but couldn't quite find a satisfying solution to it. So what I am trying to do is to censor all names from an Excel file by replacing the very next word of "herr", and "frau" (German for man and woman) to "*****". I have a working code that can replace certain words in an Excel spreadsheet but I am still trying to figure out how I can replace "the word next to the word" so to speak. Can anyone help me out with this?
Here is the code:

Sub FindandReplaceText()
'Update by Extendoffice 2018/5/24
Dim xFind As String
Dim xRep As String
Dim xRg As Range
On Error Resume Next
Set xRg =Cells
xFind = Application.InputBox("word to search:", "Kutools for Excel", , , , , 2)
xRep = Application.InputBox("word to replace:", "Kutools for Excel", , , , , 2)
If xFind = "False" Or xRep = "False" Then Exit Sub
xRg.Replace xFind, xRep, xlPart, xlByRows, False, False, False, False
End Sub

 

[JaspeR_Etzi]

A / / / / / / / | / / / / B / / / /| / / / / / / /C / / / / / / / / / | / / / /D / / / | / / / E / / / | / / / F / / /
--------------+----------------+-------------------------------+--------------+------------+-------------
row number | / / / bank / / /| / / / / / answer / / / / / / / /| category a / | category b | category c
--------------+----------------+-------------------------------+--------------+------------+-------------
/ / / 1 / / / /|example bank | I really dislike Herr Schuh / | / / / 1 / / / /| / / / /1 / / | / / / /1/ / /
--------------+----------------+-------------------------------+--------------+------------+-------------
/ / / 2 / / / /|example bank | I hate Frau Hildebert! / / / /| / / / 1 / / / /| / / / /1 / / | / / / /1/ / /
--------------+----------------+-------------------------------+--------------+------------+-------------
/ / / 3 / / / /|example bank | Whats up with Herr Olgierd?| / / / 1 / / / /| / / / /1 / / | / / / /1/ / /
--------------+----------------+-------------------------------+--------------+------------+-------------
and so on...


Ok fixed it!

 

[Peter_SSs]

Did you give the codes from post 6 or 8 a try?

 

[JaspeR_Etzi]

Another option

Sub ReplaceNextWord()
   Dim Ary As Variant, x As Variant, v As Variant
   Dim i As Long
   Ary = Range("C1", Range("C" & Rows.count).End(xlUp))
   For i = 2 To UBound(Ary)
      x = Split(Ary(i, 1), " ")
      v = Application.Match("herr", x, 0)
      If Not IsError(v) Then
         x(v) = String(Len(x(v)), "*")
         Ary(i, 1) = Join(x, " ")
      End If
      v = Application.Match("frau", x, 0)
      If Not IsError(v) Then
         x(v) = String(Len(x(v)), "*")
         Ary(i, 1) = Join(x, " ")
      End If
   Next i
   Range("C1").Resize(UBound(Ary)).Value = Application.Index(Ary, 0, 1)
End Sub

I am sorry for the late reply, was at home and my internet is currently down. This option didn't work for me as it give me the error message: "runtime error 13"

 

[JaspeR_Etzi]

Welcome to the MrExcel board!

.. a couple of more options.

Sub Censor_Herr_Frau_v1()
  Dim a As Variant
  Dim i As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    With CreateObject("VBScript.RegExp")
      .Global = True
      .IgnoreCase = True
      .Pattern = "(\b(Frau|Herr)\b )([^ ]+)"
      For i = 1 To UBound(a)
        a(i, 1) = .Replace(a(i, 1), "$1*****")
      Next i
    End With
    .Value = a
  End With
End Sub


Sub Censor_Herr_Frau_v2()
  Dim a As Variant, wrds As Variant
  Dim i As Long, j As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      wrds = Split(a(i, 1))
      For j = 0 To UBound(wrds) - 1
        If LCase(wrds(j)) = "frau" Or LCase(wrds(j)) = "herr" Then wrds(j + 1) = "*****"
      Next j
      a(i, 1) = Join(wrds)
    Next i
    .Value = a
  End With
End Sub

The second option runs perfect and does exactly what I wanted it to do after modifying the range to my needs! Thank you all so much, I have never been on a forum like this but it truly amazes me that there are so many helpful people out there!
Thank you so much for your help and time and have an awesome day!

 

[Peter_SSs]

You're welcome.

I think you should find that the first code should also work for you (unless perhaps using Excel on a Mac).

 

[Peter_SSs]

Two very good points Peter.
Amended code

Now it deletes the Frau & Herr but not the name.

 

[JaspeR_Etzi]

You're welcome.

I think you should find that the first code should also work for you (unless perhaps using Excel on a Mac).

Oh I noticed, I just put in the wrong range at first haha

 

[Peter_SSs]

Oh I noticed, I just put in the wrong range at first haha

Cheers

 

[Fluff]

Now it deletes the Frau & Herr but not the name.

Oops