none
Replace all letters in a string RRS feed

  • Question

  • Thanks for taking the time to read my question.

    I want to get rid of all letters [A-Z][a-z] and all spaces " " from a string.

    I've tried:

    Replace("123 abCD",[A-Z][a-z]" ","")

    but this errors out. I don't really want to loop through all characters in the string and check each one... do I have to?

    Thanks,
    Brad

    Wednesday, September 2, 2015 6:06 PM

Answers

  • Hi Brad,

    Assuming (and this is a big assumption) that all your data starts with numbers, and you only want the first group of numbers returned, then you should be able to use the Val() function. For example:

    Val("123 abCD") will return 123.

    Val("123 456") will also return 123 because of the space character.

    However, Val("abCD 123") won't return 123 because the string didn't start with a number.

    Hope that helps...

    • Marked as answer by mbrad Wednesday, September 2, 2015 8:04 PM
    Wednesday, September 2, 2015 7:20 PM

All replies

  • REGEX VBA- The Definitive guide

    http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

    Wednesday, September 2, 2015 6:38 PM
  • Here are two different methods for handling your request:

    Function ReplaceAlphaAndSpace(Expression As String) As String
    
        Dim strReturn As String
        Dim intPos As Integer
        Dim strChar As String
        
        strReturn = Expression
        
        For intPos = 1 To 26
            strChar = Chr(intPos + 64)
            strReturn = Replace(strReturn, strChar, "", , , vbTextCompare)
        Next lngPos
        strReturn = Replace(strReturn, " ", "")
        
        ReplaceAlphaAndSpace = strReturn
        
    End Function
    
    Function ReplaceAlphaAndSpaceII(Expression As String) As String
    
        Dim strReturn As String
        Dim lngPos As Long
        Dim strChar As String
        Dim lngLen As Long
        
        strReturn = Expression
        
        lngLen = Len(Expression)
        
        For lngPos = 1 To lngLen
            strChar = Mid(Expression, lngPos, 1)
            If (IsAlpha(strChar) Or strChar = " ") Then
                strReturn = Replace(strReturn, strChar, "", , , vbTextCompare)
            End If
        Next lngPos
        
        ReplaceAlphaAndSpaceII = strReturn
        
    End Function
    
    Function IsAlpha(Character As String) As Boolean
        
        On Error GoTo Err_Process
        
        Dim blnReturn As Boolean
        Dim intAscii As Integer
        
        blnReturn = False
        
        intAscii = Asc(Character)
        If (intAscii > 64 And intAscii < 91) Or (intAscii > 96 And intAscii < 123) Then
           blnReturn = True
        End If
        
    Exit_Process:
        IsAlpha = blnReturn
        Exit Function
        
    Err_Process:
        Resume Exit_Process
        
    End Function
    The first procedure is the most efficient, however the second gives an example of how to recognize alpha characters in your string.


    • Edited by RunningManHD Wednesday, September 2, 2015 7:28 PM
    Wednesday, September 2, 2015 7:03 PM
  • Hi Brad,

    Assuming (and this is a big assumption) that all your data starts with numbers, and you only want the first group of numbers returned, then you should be able to use the Val() function. For example:

    Val("123 abCD") will return 123.

    Val("123 456") will also return 123 because of the space character.

    However, Val("abCD 123") won't return 123 because the string didn't start with a number.

    Hope that helps...

    • Marked as answer by mbrad Wednesday, September 2, 2015 8:04 PM
    Wednesday, September 2, 2015 7:20 PM
  • Buy a lottery ticket!! ;-)

    All my values start with numbers and then have the spaces and letters. This will work perfectly.

    Thanks to everyone for your replies. I appreciate it.

    Brad

    Wednesday, September 2, 2015 8:06 PM
  • Hi Brad. Glad to hear it worked out for you. Good luck with your project.
    Wednesday, September 2, 2015 8:15 PM