Forum Discussion
VBA_ENTHU1988
Feb 12, 2024Copper Contributor
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
- NikolinoDEGold Contributor
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:
- 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.
- It defines the special characters to be replaced (specialChars) and the character to replace them with (replaceChar). You can modify these variables as needed.
- It loops through each cell in the specified range and checks each character in the cell's value.
- 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_ENTHU1988Copper Contributor
Hi NikolinoDE ,
Thank you so much for the effort and time
and Apology I did not explain my full requirement.- replacement of special character should be with its ASCII code eg. + with 43, Space with 32 and so on..
- 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
- NikolinoDEGold Contributor
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:
- Replace special characters with their corresponding ASCII codes.
- Format the text according to your specified format for database insertion, using ||CHR(ASCII code)|| for special characters.
- 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.