Need a vba code to find ascii characters

Hi,

Could you explain what you are trying to do?

Regards

I have a column of strings containing !,@,#,$,%,^,&,*,(,),...... Now in the adjacent column, I need to quote out the type of special character found.

EX: in A1 I have @Apple!# then in B1 I shoud get @,!,# found.

 I am attaching a file for reference!
sheet.xlsx

So you want all non alphabetical characters extracted from the strings in column A?

Hi,

pls try

Sub Macro1()
Dim regExp As Object
Set regExp = CreateObject("vbscript.regexp")

With regExp
    .Pattern = "[^A-Za-z0-9]*"
    .Global = True
    For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    strRes = ""
        Set matches = .Execute(c)
        For Each Match In matches
            strRes = strRes & Match
        Next
        c.Offset(, 1) = "found " & strRes
    Next
End With
End Sub

Select allOpen in new window

Regards

Hi, That's a nice solution, but a cell contains niños then,I got  "found ñ" which is valid. So is there a way to exclude them? I need only special characters. (ñ => valid in my business requirement). Also when there are no ascii characters in a particular cell, I am getting simply 'found'. Is there a way to exclude that thing?

then try

Sub Macro1()
Dim regExp As Object
Set regExp = CreateObject("vbscript.regexp")

With regExp
    .Pattern = "[^0-9A-Za-zÀ-ÿ]*"
    .Global = True
    For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    strRes = ""
        Set matches = .Execute(c)
        For Each Match In matches
            strRes = strRes & Match
        Next
        c.Offset(, 1) = "found " & strRes
    Next
End With
End Sub

Select allOpen in new window

Thank You RGONZO. thanks a lot. But still I could see a lag in applying formula for 3L rows of data.

Thanks,
Sai

Thank You Rgonzo.. it worked! :) I am just a beginner :)

Starting with RGonzo's code, I get this tweaked code.
Optimizations:
* use regexp.test() to determine whether to even parse the string
* assigned cell value to local string variable
* only concatenate if the character hasn't already been concatenated
* Changed pattern to look for individual characters

Option Explicit

Sub Macro1()
    Application.ScreenUpdating = False
    Dim regExp As Object
    Dim strCellValue As String
    Dim c As Range
    Dim strRes As String
    Dim matches As Object
    Dim Match As Object
    
    Set regExp = CreateObject("vbscript.regexp")
    
    With regExp
        .Pattern = "[^0-9A-Za-zÀ-ÿ]"
        .Global = True
        For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
            strCellValue = c.Value
            If .Test(strCellValue) Then
                strRes = ""
                Set matches = .Execute(strCellValue)
                For Each Match In matches
                    If InStr(strRes, Match) = 0 Then
                        strRes = strRes & Match
                    End If
                Next
                
                c.Offset(, 1) = "found " & strRes
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Hi aikimark, nice solution. But what if I want to find some string "xyz" along with those special characters? how to include them in the regex pattern?

Please post some examples of strings and what a function should return

Hi, I am attaching a sample file. Please have a look at it.
sample.xlsm

Also, you should decide what you want to do about comma characters if you are looking for the words, since the words are comma separated and there might be a comma in the column A

got it!  thank you. :)

How to find for japanese text?  should I convert to unicode?

Well, they would certainly be encoded/represented as unicode characters unless you have one of the UTF encodings.  I don't know how to distinguish between Japanese and other language characters.

If there is a range of values, then you might use the AscW() function to compare the unicode value (not char) against that range.

nice one thank you..

aikimark, how to find \ and "" (backward slash and double quotes) using regex?

aikimark, In 'A' column i have data ホーム in what way should I write regex to have a match?

1. Please post data
2. What do you need to do when you encounter such characters?

@aikimark, that is related to the same business requirement, when ever I encounter that text as mentioned above, I should get found ホーム . So I was unable to write a regex in vba. Because I was unable to input japanese text. Is there a way?

As I posted in that thread, you may have to use the ChrW() function to create the string through concatenation.