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

Question

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 !

Tarik · Accepted Answer · 2013-08-02 02:54:58Z

2

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

answered Aug 2, 2013 at 2:54

Tarik

11.2k2 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tarik Over a year ago

Note that you have to handle the case where VA is at the beginning or the end of the string. A single loop on all separator characters should do it.

PatricK · Accepted Answer · 2013-08-02 03:27:50Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related