Solved

Need a vba code to find ascii characters

Posted on 2016-10-21
27
58 Views
Last Modified: 2016-11-11
Hi there, I need a vba code to find ascii characters (all special characters you see on the keyboard). I wrote nested if's but that is running very slow when the data is around 1M. So can any one suggest a way?
0
Comment
Question by:sai k
  • 12
  • 8
  • 4
  • +1
27 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41853378
Hi,

Could you explain what you are trying to do?

Regards
0
 

Author Comment

by:sai k
ID: 41853420
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 41853536
So you want all non alphabetical characters extracted from the strings in column A?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41853566
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

Open in new window

Regards
1
 

Author Comment

by:sai k
ID: 41853595
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?
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41853604
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

Open in new window

0
 

Author Comment

by:sai k
ID: 41853656
Thank You RGONZO. thanks a lot. But still I could see a lag in applying formula for 3L rows of data.

Thanks,
Sai
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 300 total points (awarded by participants)
ID: 41853670
Should be swifter
Sub Macro1()
Application.ScreenUpdating = False
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
Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:sai k
ID: 41853794
Thank You Rgonzo.. it worked! :) I am just a beginner :)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41853961
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

Open in new window

0
 

Author Comment

by:sai k
ID: 41855194
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?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41855197
Please post some examples of strings and what a function should return
0
 

Author Comment

by:sai k
ID: 41855214
Hi, I am attaching a sample file. Please have a look at it.
sample.xlsm
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 200 total points (awarded by participants)
ID: 41855241
Please test this.  You are treating blank (space) characters inconsistently.  You can comment/uncomment the .pattern= statements to include space characters in your found list or ignore them.
Option Explicit

Sub Q_28977934()
    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 = "learn|rate|how|[^ 0-9A-Za-zÀ-ÿ]"    'ignore blanks
        '.Pattern = "learn|rate|how|[^0-9A-Za-zÀ-ÿ]"    'treat blanks as invalid
        .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
                        If Len(Match) = 1 Then
                            'prepend character
                            strRes = Match & strRes
                        Else
                            'append comma-separated string
                            strRes = strRes & "," & Match
                        End If
                        
                    End If
                Next
                
                c.Offset(, 1) = "found " & strRes
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 41855242
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
0
 

Author Comment

by:sai k
ID: 41855247
got it!  thank you. :)
0
 

Author Comment

by:sai k
ID: 41855249
How to find for japanese text?  should I convert to unicode?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41855272
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.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 200 total points (awarded by participants)
ID: 41855275
From https://en.wikipedia.org/wiki/Japanese_writing_system
I see that there are defined ranges:
U+4E00–U+9FBF Kanji
U+3040–U+309F Hiragana
U+30A0–U+30FF Katakana
0
 

Author Comment

by:sai k
ID: 41855289
nice one thank you..
0
 

Author Comment

by:sai k
ID: 41855320
aikimark, how to find \ and "" (backward slash and double quotes) using regex?
0
 

Author Comment

by:sai k
ID: 41855356
aikimark, In 'A' column i have data ホーム in what way should I write regex to have a match?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41855444
1. Please post data
2. What do you need to do when you encounter such characters?
0
 

Author Comment

by:sai k
ID: 41855770
@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?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41855954
As I posted in that thread, you may have to use the ChrW() function to create the string through concatenation.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question