Solved

Need a vba code to find ascii characters

Posted on 2016-10-21
27
51 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 48

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
 
LVL 48

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 48

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 48

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now