Solved

Need a vba code to find ascii characters

Posted on 2016-10-21
27
61 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
  • 4
  • +1
27 Comments
 
LVL 51

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 34

Expert Comment

by:Norie
ID: 41853536
So you want all non alphabetical characters extracted from the strings in column A?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 51

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 51

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 51

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

752 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