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

[VBA_ENTHU1988]

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

[#2]

@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.

[#3]

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

           

[#4]

@VBA_ENTHU1988 

I think I now understand your requirements better.

Hope so

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.