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 thi...
Macros and VBA
VBA_ENTHU1988's avatar
VBA_ENTHU1988
Copper Contributor
Feb 15, 2024

 

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
Feb 16, 2024

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