none
apostrophe and quotation marks do not recognize properly in vba RRS feed

  • Question

  • I receive a list of part nos. (e.g. *12-E000 +=).  They can either be in numeric or alphanumeric format attached with some useless spaces & symbols attached.   Those useless symbols and spaces need to be cleaned up such that the final part nos. only retain pure alphabets (if any) & numbers for further use.

     

    e.g. Part no. in Column A, [*12-E000 +=] is cleaned up to [‘12E000]

    Adding apostrophe at the beginning is important to make sure Excel reads this part no. as text.  (n.b. 12E000 can be read as 12 x 10 power 3 if it is regarded as number)

     

    Based on above, below shows the Excel formula written to get a clean Part no. :-

     =TRIM(UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(""'""&(A2),"-","")," ",""),"*",""),"+",""),"=","")))

     

    I need to put this in vba using the syntax  .Formula = “ xxx formula content xxx ” but it doesn’t work with error message.  Is there any suggestion to fix this problem?  Thanks.

    <iframe class="gdict-voice-frame" height="2" width="5"></iframe>
    Friday, July 8, 2016 6:45 AM

Answers

  • For example:

    Sub Cleanup()
        Dim c As Range
        Dim s As String
        Dim v As Variant
        Application.ScreenUpdating = False
        Range("A2:A100").NumberFormat = "@"
        For Each c In Range("A2:A100")
            s = c.Value
            For Each v In Array(" ", "-", "*", "+", "=")
                s = Replace(s, v, "")
            Next v
            c.Value = s
        Next c
        Application.ScreenUpdating = True
    End Sub

    Change A2:A100 to the range you want to clean up.

    Or if you only want to leave letters and digits:

    Sub Cleanup()
        Dim c As Range
        Dim s As String
        Dim t As String
        Dim i As Long
        Application.ScreenUpdating = False
        Range("A2:A100").NumberFormat = "@"
        For Each c In Range("A2:A100")
            s = c.Value
            t = ""
            For i = 1 To Len(s)
                Select Case Asc(Mid(s, i, 1))
                    ' Keep only digits, upper case letters and lower case letters
                    Case 48 To 57, 65 To 90, 97 To 122
                        t = t & Mid(s, i, 1)
                End Select
            Next i
            c.Value = t
        Next c
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 8, 2016 7:57 AM
  • Alternatively:

    Private Sub Main()
        Debug.Print FixText("_,piece PIECE_1345")
    End Sub
    
    Private Function FixText(pText As String) As String
        Static RegExp As Object
        Dim Result As String
        
        If RegExp Is Nothing Then
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Global = True
            RegExp.IgnoreCase = False
            RegExp.Pattern = "[^a-zA-Z0-9]"
        End If
        
        Result = RegExp.Replace(pText, "")
        
        FixText = Result
    End Function


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by David_JunFeng Friday, July 15, 2016 6:02 AM
    Friday, July 8, 2016 11:59 AM

All replies

  • For example:

    Sub Cleanup()
        Dim c As Range
        Dim s As String
        Dim v As Variant
        Application.ScreenUpdating = False
        Range("A2:A100").NumberFormat = "@"
        For Each c In Range("A2:A100")
            s = c.Value
            For Each v In Array(" ", "-", "*", "+", "=")
                s = Replace(s, v, "")
            Next v
            c.Value = s
        Next c
        Application.ScreenUpdating = True
    End Sub

    Change A2:A100 to the range you want to clean up.

    Or if you only want to leave letters and digits:

    Sub Cleanup()
        Dim c As Range
        Dim s As String
        Dim t As String
        Dim i As Long
        Application.ScreenUpdating = False
        Range("A2:A100").NumberFormat = "@"
        For Each c In Range("A2:A100")
            s = c.Value
            t = ""
            For i = 1 To Len(s)
                Select Case Asc(Mid(s, i, 1))
                    ' Keep only digits, upper case letters and lower case letters
                    Case 48 To 57, 65 To 90, 97 To 122
                        t = t & Mid(s, i, 1)
                End Select
            Next i
            c.Value = t
        Next c
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 8, 2016 7:57 AM
  • Alternatively:

    Private Sub Main()
        Debug.Print FixText("_,piece PIECE_1345")
    End Sub
    
    Private Function FixText(pText As String) As String
        Static RegExp As Object
        Dim Result As String
        
        If RegExp Is Nothing Then
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.Global = True
            RegExp.IgnoreCase = False
            RegExp.Pattern = "[^a-zA-Z0-9]"
        End If
        
        Result = RegExp.Replace(pText, "")
        
        FixText = Result
    End Function


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by David_JunFeng Friday, July 15, 2016 6:02 AM
    Friday, July 8, 2016 11:59 AM