replace a whole word in vba

Posted on 2014-08-28
Last Modified: 2014-08-29
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

Question by:PeterBaileyUk
    LVL 95

    Expert Comment

    by:Lee W, MVP
    Maybe I'm missing something - have you thought about using the vba Replace function?


    Author Comment

    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.
    LVL 95

    Expert Comment

    by:Lee W, MVP
    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. "

    Author Comment

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

    "my string st"

    what I would like is:
    "my string"
    LVL 95

    Expert Comment

    by:Lee W, MVP
    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.
    LVL 26

    Accepted Solution

    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


    Author Closing Comment

    went with this function as it was small and efficient and I can call it when needed.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now