replace a whole word in vba

I have a table that has a column called lookfor it has string type.

I have another table with a string field made of words.

If i find tableA.lookfor in the table2 field I want to remove the whole word in table 2. for example

look for value "323"
table 2 string =3 SERIES SALOON 323i 4dr

323i would be replaced by nothing ""

not sure how to achieve that.

I have two recordsets so can get to the data:


Private Sub BtnRemoveModelData_Click()
Dim Db As DAO.Database
Set Db = CurrentDb()
Dim RstAutoRange As DAO.Recordset
Set RstAutoRange = Db.OpenRecordset("TblProcess")

Dim RstWordGroups As DAO.Recordset
Set RstWordGroups = Db.OpenRecordset("ClientWordGroups")
Dim ClientWords As Variant
 Dim x As Long
Dim WordToRemove As String

DoCmd.SetWarnings False

With RstAutoRange

    countrecords = .RecordCount

 'cycle through the recordset
    Do Until RstAutoRange.EOF

        With RstWordGroups
            Do Until RstWordGroups.EOF
                If (InStr(RstWordGroups.Fields("ConcatenatedUnwantedRemoval").Value, RstAutoRange.Fields("LookFor").Value) <> 0) And (RstWordGroups.Fields("MarqueAlias").Value = RstAutoRange.Fields("Marque").Value) Then
                'not found
                End If
        End With

End With

End Sub


Open in new window

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
Maybe I'm missing something - have you thought about using the vba Replace function?

PeterBaileyUkAuthor Commented:
yes I did that first, but i want to eliminate the whole word so if i say find 325 but the word in the string to search has 325st how do i replace the 325st as a whole and not just the substring.
Lee W, MVPTechnology and Business Process AdvisorCommented:
If it's a "word" then it's surrounded by spaces.

replace " 325 " with " something "

MAYBE you next a couple replaces to cover " 325, " and " 325. "
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PeterBaileyUkAuthor Commented:
if i did replace("my string 325st","325","") i would get

"my string st"

what I would like is:
"my string"
Lee W, MVPTechnology and Business Process AdvisorCommented:
I see your issue now.

I would suggest looking over the solution here:

It appears you will have to use a RegEx expression in your replacing.
Try this function:
Function CustomRemove(strInput As String, strSearch As String) As String

    Dim strArray() As String
    Dim i As Long
    Dim strTemp As String

    strArray = Split(strInput, " ")

    For i = LBound(strArray) To UBound(strArray)
        If InStr(1, strArray(i), strSearch) > 0 Then
            strArray(i) = ""
        End If
        strTemp = strTemp & strArray(i) & " "
    CustomRemove = Replace(strTemp, "  ", " ")
End Function

Open in new window

Sample usage:
msgbox CustomRemove("3 SERIES SALOON 323i 4dr","323")

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
went with this function as it was small and efficient and I can call it when needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.