VBA code to strip # sign

[HWL]

I have code I made to strip all letters from a cell. I made a user function called "letterout" -- see below, however I also want to strip the pound sign out of the string.

Function LetterOut(rng As Range)
Dim i As Integer
For i = 1 To Len(rng)
Select Case Asc(Mid(rng.Value, i, 1))
Case 0 To 64, 123 To 197
LetterOut = LetterOut & Mid(rng.Value, i, 1)
End Select
Next i
End Function
​

I imagine it is another Case number but have no idea which. Help?

 

[diddi]

this might help...
http://www.asciitable.com/

 

[HWL]

this might help...
http://www.asciitable.com/

yeah saw that, has 35,23, 043 for #

I would think that would be covered by Case 0 to 64?

 

[Rick Rothstein]

Here is another function that will do what you want...

Function LettersOut(rng As Range) As String
    Dim X As Long
    Const CharactersToRemove As String = "[A-Za-z#]"
    LettersOut = rng.Value
    For X = 1 To Len(LettersOut)
        If Mid(LettersOut, X, 1) Like CharactersToRemove Then Mid(LettersOut, X) = Chr(1)
    Next
    LettersOut = Replace(LettersOut, Chr(1), "")
End Function

You can control the characters to remove using the CharactersToRemove constant (the Const statement)... the list using dashes to indicate ranges coupled with individual characters (see the help files for the VB "Like Operator" for all the rules on how to specify the characters.

 

[wigi]

I would think that would be covered by Case 0 to 64?

Yes, but 0-64 are the ones you collect in the string you're composing...

 

[HWL]

Here is another function that will do what you want...

Function LettersOut(rng As Range) As String
    Dim X As Long
    Const CharactersToRemove As String = "[A-Za-z#]"
    LettersOut = rng.Value
    For X = 1 To Len(LettersOut)
        If Mid(LettersOut, X, 1) Like CharactersToRemove Then Mid(LettersOut, X) = Chr(1)
    Next
    LettersOut = Replace(LettersOut, Chr(1), "")
End Function

You can control the characters to remove using the CharactersToRemove constant (the Const statement)... the list using dashes to indicate ranges coupled with individual characters (see the help files for the VB "Like Operator" for all the rules on how to specify the characters.

I used the code exactly as you posted but the # is still there. Do I need to do more?

 

[Rick Rothstein]

I used the code exactly as you posted but the # is still there. Do I need to do more?

In my tests, the function removes letters and the # sign. Can you send your workbook to me so I can see what is going on directly? My email address is rickDOTnewsATverizonDOTnet (just replace the upper case letters with the symbols they spell out).

 

[HWL]

Yes, but 0-64 are the ones you collect in the string you're composing...

Hmm, now I tried 48 to 57 which from what I can see should be only numbers, but I'm still getting the #. Should I be using the DEX of HX column and do I need to reset the function some how to take effect?

 

[HWL]

In my tests, the function removes letters and the # sign. Can you send your workbook to me so I can see what is going on directly? My email address is rickDOTnewsATverizonDOTnet (just replace the upper case letters with the symbols they spell out).

Ah, now I see what happened, you changed the function name to letterSout when it was originally letterout. Your's makes more sense and now works correct. Thanks for the offer to look at the code!

 

[Rick Rothstein]

Ah, now I see what happened, you changed the function name to letterSout when it was originally letterout.

I'm sorry... I did not notice you called the function by a singular name... I thought I read it as being plural (because you were removing more than one character). I'm glad you spotted that. Again, sorry for doing that to you.