CODEWITHSUNDEEP
stringvba
Ali_R4v3n
Ali_R4v3n

Reputation: 377

Remove surrounding single quotes from a string in VBA

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

Upvotes: 1

Views: 2805

Answers (2)

Michael Lee Marburger
Michael Lee Marburger

Reputation: 1

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

Upvotes: 0

ThunderFrame
ThunderFrame

Reputation: 9471

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

Upvotes: 1

Related Questions