Removing embedded spaces and special characters from a string 4

[SteveMillman]

Hi.

Is there an easy way to remove spaces (or special characters) from a string? I noticed that the trim
commands are geared towards only removing leading and trailing spaces. I have a file name being passed to me that contains a colon and also some spaces that acrobat
does not particularly like, when I try to build pdf members.
I am used to COBOL allow you to examine a string replacing
characters (with nothing.. in some cases, to eliminate certain embedded values); but I havent found a similar function or command in VBA.
I am really new to VBA and would rather not have to setup
loops to look a character at a time and output characters conditionally, if they are not spaces or special characters.

Thanks for your help,

Steve

 

[RickSpr]

There's no built-in function to do this, but you can easily write one that does it in one pass:

    Public Function DeleteEmbedded(CharsToDelete As String, FromString As String)
        Dim i As Integer, s As String

        For i = 1 To Len(FromString)
            If InStr(CharsToDelete, Mid$(FromString, i, 1)) = 0 Then
                s = s & Mid$(FromString, i, 1)
            End If
        Next i
        DeleteEmbedded = s
    End Function

This function allows you to specify multiple unwanted characters in the CharsToDelete argument. Rick Sprague

 

[SteveMillman]

Rick,

But if we wrote a function like you describe, would be be able to access it in another function? Its this email building function that we are working with that is encountering the problem with the spaces embedded in the "report name" field. Would we somehow run the one function from the other?

Steve

 

[RickSpr]

Wow, you're really new to VBA, aren't you? Of course you can call one function from another! You just have to make sure the called function is in a standard module, and is a Public function (the default) rather than a Private one.

You should be able to get everything you need from the help file. Rick Sprague

 

[MichaelRed]

Public Function basCleanStr(strIn As String) As String

    'Michael Red    5/20/2002   Remove "Un-Wanted" Characters from String

    Dim Idx As Long
    Dim MyChr As String * 1
    Dim strOut As String

    For Idx = 1 To Len(strIn)

        MyChr = Mid(strIn, Idx, 1)
        Select Case MyChr

            Case "0" To "9"
                strOut = strOut & MyChr

            Case "A" To "Z"
                strOut = strOut & MyChr

            Case "a" To "z"
                strOut = strOut & MyChr

        End Select

    Next Idx

    basCleanStr = strOut

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[scking]

I like them both better than the one(s) I've built. Elegance of code always does that to me. It's also good to view a solution from another persons perspective sometimes.

Steve King Growth follows a healthy professional curiosity