Remove surrounding single quotes from a string in VBA

Question

I wrote a simple code below which process string values “words”, if the word has a leading single quote (example: ‘em ) or a trailing one (example: tryin’) or both (example: ‘this’ ) then it removes them , if there are more than one instance of the mark it gets removed too. but if the quote mark is in the middle leave it alone, examples (America's, aren't, can't)

How do I achieve the same function differently and efficiently? And I don’t want to use a recursive procedure to do it.

Sub RemoveSurroundingSingleQuotes()
    Dim arr(), wrd$
    arr = Array("'wrd1'", "''wrd3''")
    For i = 0 To UBound(arr)
        wrd = arr(i)
        Debug.Print "word before: " & wrd
RemoveLeft:
        If Left(wrd, 1) = "'" Then wrd = Mid(wrd, 2)
        'check the left side again
        If Left(wrd, 1) = "'" Then GoTo RemoveLeft:
RemoveRight:
        If Right(wrd, 1) = "'" Then wrd = Mid(wrd, 1, Len(wrd) - 1)
        'check the Right side again
        If Right(wrd, 1) = "'" Then GoTo RemoveRight:
        Debug.Print "word after: " & wrd & vbCr & "--------------"
    Next
End Sub

regular expressions can be used with pattern `^'*(.+)'*$` and replacement `$1` http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops and http://analystcave.com/excel-regex-tutorial/ — shibormot, Mar 20 '16 at 11:06
@shibormot, what about speed, is it faster ? if i use the RegEx, i prefer to use late binding so i need to create an obj `Dim mRegEx As Object` then bind it `Set mRegEx = CreateObject("VBScript.RegExp")` — Ali_R4v3n, Mar 20 '16 at 11:20
if you have precompiled RE inside you sub (ie not creating RE inside, but create it globally before use) it should be pretty fast. If you need speed of light here, may be manual code will be faster. But tests for sub velocity are simple to write — shibormot, Mar 20 '16 at 11:20
you mean to create a public RE object on modular level then use it?. its a good idea but i wanted to keep it as simple and efficient as possible and to stay away from objects cuz i'm using lots of them and this code is part of something bigger :( — Ali_R4v3n, Mar 20 '16 at 11:25

score 1 · Answer 1 · answered Mar 20 '16 at 11:04

Here's some code that uses While loops to find the start and end positions, so it only does the string reassignment once, after it knows the start and end positions.

Sub RemoveSurroundingSingleQuotes()
    Dim arr() As Variant
    Dim wrd As String
    Dim iStart As Long
    Dim iEnd As Long

    arr = Array("'wrd1'", "''wrd3''")

    For i = LBound(arr) To UBound(arr)
        wrd = arr(i)
        Debug.Print "word before: " & wrd

        iStart = 1
        iEnd = Len(wrd)
        Do While Mid(wrd, iStart, 1) = "'" And iStart < iEnd
          iStart = iStart + 1
        Loop

        Do While Mid(wrd, iEnd, 1) = "'" And iEnd > iStart
          iEnd = iEnd - 1
        Loop

        wrd = Mid(wrd, iStart, iEnd - iStart + 1)
        Debug.Print "word after: " & wrd & vbCr & "--------------"
    Next
End Sub

nice idea with while loops – Ali_R4v3n Mar 20 '16 at 12:01 — Ali_R4v3n, Mar 20 '16 at 12:01

score 0 · Answer 2 · edited Sep 20 '18 at 04:57

0

Private Function Dequoted(S As String) As String
If Left(S, 1) = """" And Right(S, 1) = """" Then Dequoted = Mid(S, 2, Len(S) - 2) Else Dequoted = S
End Function

edited Sep 20 '18 at 04:57

Pang

9,564
146
81
122

answered Sep 20 '18 at 04:32

Michael Lee Marburger

1

Remove surrounding single quotes from a string in VBA

2 Answers2