CODEWITHSUNDEEP
excelvbareplace
Phalanx
Phalanx

Reputation: 1217

Excel - VBA : Make the "replace" function more specific

I am currently encountering a problem which doesn't seem that hard to fix but, yet, I can't find a clean way of doing it on my own.

I am using the "Replace" function to change some expressions in a sentence typed by an user. For example, if the user types "va", I want it to be turned into "V. A." instead so it will match more easily with my database for further operations.

Here is my simple code to do it : 

sMain.Range("J3").Replace "VA", "V. A."

It works well.
Problem is, it's not only spotting "VA" as an individual expression, but also as a part of words.

So if my user types "Vatican", it's gonna turn it into : "V. A.tican"... which of course I don't want.

Do you know how to easily specify my code to make it ONLY consider replacing the whole words matching the expression? (I have dozens of lines of these replacement so ideally, it would be better to act directly on the "replace" functions - if possible).

Thanks in advance !

Upvotes: 0

Views: 3711

Answers (2)

PatricK
PatricK

Reputation: 6433

Can split it up and check each word. I have put it into a function for easy of use and flexibility.

Function ReplaceWordOnly(sText As String, sFind As String, sReplace As String) As String
    On Error Resume Next
    Dim aText As Variant, oText As Variant, i As Long

    aText = Split(sText, " ")
    For i = 0 To UBound(aText)
        oText = aText(i)
        ' Check if starting with sFind
        If LCase(Left(oText, 2)) = LCase(sFind) Then
            Select Case Asc(Mid(oText, 3, 1))
                Case 65 To 90, 97 To 122
                    ' obmit if third character is alphabet (checked by ascii code)
                Case Else
                     aText(i) = Replace(oText, sFind, sReplace, 1, -1, vbTextCompare)
            End Select
        End If
    Next
    ReplaceWordOnly = Join(aText, " ")
End Function

Example output:

?ReplaceWordOnly("there is a vatican in vA.","Va","V. A.")
there is a vatican in V. A..

Upvotes: 2

Tarik
Tarik

Reputation: 11209

Do this:

sMain.Range("J3").Replace " VA ", "V. A."

then handle the cases where the original string starts or ends with VA also, handle all cases of separators which could be (for example) tab, space or comma. To do that:

const nSep As Integer = 3
Dim sep(nSep) As String
sep(1) = " "
sep(2) = vbTab
sep(3) = ","
for i=1 to nSep
    for j=1 to nSep
        sMain.Range("J3").Replace sep(i) & "VA" & sep(j), "V. A."
    next
next

Upvotes: 2

Related Questions