Reputation: 377
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
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
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