Solved

Need a vba code to find ascii characters

Posted on 2016-10-21
27
68 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 52

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 52

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 52

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 52

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 46

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 46

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 46

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 46

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 46

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 46

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 46

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 46

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

627 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