Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need a vba code to find ascii characters

Posted on 2016-10-21
27
Medium Priority
?
89 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
Independent Software Vendors: 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!

 
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 1200 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 800 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 800 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

721 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