• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

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

  • 3
  • 3
1 Solution
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. "
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now