Remove Leading and Trailing Commas, Colons, and Quotes

[lourdesroxenne]

Hello. Good day. I am new in excel so I hope you could help me with this..

I have been over the web but I can't find the right solution to my problem..
So given I have a string ,",,abcdef :, ghijk,",:, i want the output string to be abcdef :, ghjk
I think this has to do with regex patterns but I am not sure though..
Btw, I am using VBA.

Thanks in advance..
Cheers

 

[par60056]

I wrote this little function that will trim it for you.

Function myXtrim(inVal As String) As String
Dim retval As String
If (InStr(""",: ", Left(inVal, 1)) > 0) Then
    retval = myXtrim(Mid(inVal, 2))
ElseIf (InStr(""",: ", Right(inVal, 1)) > 0) Then
    retval = myXtrim(Mid(inVal, 1, Len(inVal) - 1))
Else
    retval = inVal
End If
myXtrim = retval
End Function

There may be an easier way but this was simple to write.

 

[lourdesroxenne]

Hello par60056.

Function works quite good except that there is out of space in the stack during the recursion.

 

[Rick Rothstein]

Here is a function which will trim your text so that it starts and ends with a letter of the alphabet (no matter what leading or ending characters the original string contains)...

Function TrimToLetters(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[A-Za-z]" Then
      S = Mid(S, X)
      Exit For
    End If
  Next
  For X = Len(S) To 1 Step -1
    If Mid(S, X, 1) Like "[A-Za-z]" Then
      S = Left(S, X)
      Exit For
    End If
  Next
  TrimToLetters = S
End Function

 

[lourdesroxenne]

Hi Rick. Thanks for this, but the text could end in numbers of closing parenthesis..

 

[par60056]

Hello par60056.

Function works quite good except that there is out of space in the stack during the recursion.

ok Here is another without recursion

Function myXtrim(inVal As String) As String
Dim retVal As String
retVal = inVal
While (InStr(""",: ", Left(retVal, 1)) > 0) And Len(retVal) > 1
    retVal = Mid(retVal, 2)
Wend
While (InStr(""",: ", Right(retVal, 1)) > 0) And Len(retVal) > 1
    retVal = Mid(retVal, 1, Len(retVal) - 1)
Wend
myXtrim = retVal
End Function

 

[Rick Rothstein]

Hi Rick. Thanks for this, but the text could end in numbers of closing parenthesis..

Was the red text supposed to be "or"? Noting that you did not mention the digits or parentheses originally (so I did not know to account for them), assuming the "of" should have been "or", then try this function...

Function TrimToLetters(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[A-Za-z]" Then
      S = Mid(S, X)
      Exit For
    End If
  Next
  For X = Len(S) To 1 Step -1
    If Mid(S, X, 1) Like "[A-Za-z0-9)]" Then
      S = Left(S, X)
      Exit For
    End If
  Next
  TrimToLetters = S
End Function

 

[lourdesroxenne]

Thanks guys. Both solutions work for me

Cheers

 

[par60056]

glad it helped.