Forum Discussion

VBA_ENTHU1988's avatar
VBA_ENTHU1988
Copper Contributor
Feb 12, 2024

Macros to find all Special Character in all columns and rows of excel

I have a excel with 1800 rows and 16 columns

Field are like phone number which starts with +
Filed of Email has @ and .
similarly it has &, $,#,line break,-,etc.
Now, ask is we need to upload this excel in one of our Database table but to avoid SQL injection security settings do not allow to upload excel with special characters except ;,:,- all others are either converted to | or removed.

 

I need help in writing a VBA macros which identifies all special character in the excel sheet and replace them maybe with ASCII chr(43) for + , or _plus so that we can load in DB and replace in reporting layer.

 

Kindly help

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    VBA_ENTHU1988 

    You can use VBA to loop through all cells in your Excel sheet, identify special characters, and replace them with your desired characters. Below is a VBA macro that does this:

    Vba Code is untested, please backup you file first.

    Sub ReplaceSpecialCharacters()
        Dim ws As Worksheet
        Dim rng As Range
        Dim cell As Range
        Dim specialChars As String
        Dim replaceChar As String
        
        ' Define the worksheet to be processed
        Set ws = ThisWorkbook.Worksheets("Sheet1") ' Update "Sheet1" with your sheet name
        
        ' Define the range to be processed (assuming data starts from A1)
        Set rng = ws.UsedRange
        
        ' Define the special characters to be replaced
        specialChars = "~!@#$%^&*()-_=+[{]}\|;:',<.>/?"
        
        ' Define the character to replace special characters with
        replaceChar = "|" ' You can change this to whatever character you want
        
        ' Loop through each cell in the range
        For Each cell In rng
            If Not IsEmpty(cell.Value) Then
                ' Loop through each character in the cell's value
                For i = 1 To Len(cell.Value)
                    ' Check if the character is a special character
                    If InStr(specialChars, Mid(cell.Value, i, 1)) > 0 Then
                        ' Replace the special character with the desired character
                        cell.Value = Replace(cell.Value, Mid(cell.Value, i, 1), replaceChar)
                    End If
                Next i
            End If
        Next cell
    End Sub

    Here is what the macro does:

    1. It defines the worksheet to be processed (ws) and the range to be processed (rng). You need to update "Sheet1" with the name of your sheet.
    2. It defines the special characters to be replaced (specialChars) and the character to replace them with (replaceChar). You can modify these variables as needed.
    3. It loops through each cell in the specified range and checks each character in the cell's value.
    4. If a special character is found, it replaces it with the desired character.

    Before running the macro, make sure to save your workbook because it will modify the data in your Excel sheet. You can run the macro by pressing Alt + F11 to open the VBA editor, inserting a new module, pasting the code into the module, and then running the macro from the Excel interface. The text, steps and code were created with the help of AI.

     

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

    • VBA_ENTHU1988's avatar
      VBA_ENTHU1988
      Copper Contributor

       

      Hi NikolinoDE ,
      Thank you so much for the effort and time
      and Apology I did not explain my full requirement.

       

      1. replacement of special character should be with its ASCII code eg. + with 43, Space with 32 and so on..
      2. Since this data needs to read by a database it needs to be converted in a certain format eg. as below
        • New York, USA - here, and space should be replaced by
        • New32York3244USA - till here I was      able achieve with your code
        • but addition as point 2 states it should in DB format like 'New'||chr(32)||'York'||CHR(32)||CHR(44)||'USA' here quotes should be on non-special character full word || for concatenation the CHR(ASCII code)

      I know its too much to ask but I am just starting to learn if you can help

                 

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        VBA_ENTHU1988 

        I think I now understand your requirements better.

        Hope so :smile:

        Below is the updated VBA macro that replaces special characters with their corresponding ASCII codes and formats the text according to your specified format for database insertion:

        Vba Code in untested, please backup your file.

         

        Sub ReplaceSpecialCharactersAndFormatForDB()
            Dim ws As Worksheet
            Dim rng As Range
            Dim cell As Range
            Dim specialChars As String
            Dim dbFormattedText As String
            Dim charAscii As Integer
            Dim charCode As String
            
            ' Define the worksheet to be processed
            Set ws = ThisWorkbook.Worksheets("Sheet1") ' Update "Sheet1" with your sheet name
            
            ' Define the range to be processed (assuming data starts from A1)
            Set rng = ws.UsedRange
            
            ' Define the special characters to be replaced
            specialChars = "~!@#$%^&*()-_=+[{]}\|;:',<.>/?"
            
            ' Loop through each cell in the range
            For Each cell In rng
                If Not IsEmpty(cell.Value) Then
                    dbFormattedText = ""
                    ' Loop through each character in the cell's value
                    For i = 1 To Len(cell.Value)
                        ' Get ASCII code of the character
                        charAscii = Asc(Mid(cell.Value, i, 1))
                        ' Check if the character is a special character
                        If InStr(specialChars, Mid(cell.Value, i, 1)) > 0 Then
                            ' Format the character as '||CHR(ASCII code)||'
                            charCode = "||CHR(" & charAscii & ")||"
                        Else
                            ' If not a special character, keep the character as is
                            charCode = Mid(cell.Value, i, 1)
                        End If
                        ' Concatenate the characters for database insertion format
                        dbFormattedText = dbFormattedText & charCode
                    Next i
                    ' Update the cell value with the formatted text
                    cell.Value = "'" & dbFormattedText & "'"
                End If
            Next cell
        End Sub

         

        The code was regenerated using the AI.

        This updated macro will:

        1. Replace special characters with their corresponding ASCII codes.
        2. Format the text according to your specified format for database insertion, using ||CHR(ASCII code)|| for special characters.
        3. Enclose the formatted text in single quotes to represent a string literal in SQL.

        You can run this macro in a similar manner as the previous one. Make sure to save your workbook before running the macro as it will modify the data in your Excel sheet.

Resources