Excel – Is there one simple formula for Excel that can identify a string contain non-ASCII Characters

microsoft excelmicrosoft-excel-2007worksheet-function

I know that using the clean formula, I can clean up some of the non-ASCII characters (such as additional non-printable ASCII control characters #0 through to #31, #129, #141, #143, #144, and #157 except #127)

I also know that I can use the SUBSTITUTE(D1,CHAR(127),"") to remove non-printable ASCII #127.

However, I cannot replace or identify non-ASCII characters inside a cell in Excel.

The following is a simple example:

Burrell's Model
Burrell’s Model

Notice that the first line is a normal ASCII string while the second line contains a non-ASCII character (the apostrophe).

May I know how to use Excel to find non-ASCII characters?

Thank you.

Update 1

According to Bandrami comments as follows:

In the broadest sense this is impossible; there are valid ASCII strings that are also valid UTF characters, so there's no way to ever know "for certain" (see the "Bush hid the facts" bug: en.wikipedia.org/wiki/Bush_hid_the_facts )

May I know how do I identify characters that is not in the following ASCII range?

Less Than 128
Not equal to 255

Best Answer

MS Office help seems to consider the apostrophe as a character that CLEAN should catch but it doesn't remove it on my machine.

MS Office help, Remove spaces and nonprinting characters from text, second to last paragraph

The 'simplest' I could find was a nested run of SUBSTITUTEs that clean anything that the CLEAN function doesn't catch.

From an old google excel group

=SUBSTITUTE(SUBSTITUTE(F17,CHAR(141),""),CHAR(143),"") etc etc

Related Solutions

Excel – Function to Evaluate a String as a Formula

EVALUATE is available in VBA in all current versions

You can include it in you VBA code, or wrap it into a simple UDF to make it available as a worksheet function

Function ev(r As Range) As Variant
    ev = Evaluate(r.Value)
End Function

It basically treats the value of the passed parameter as an Excel formula, same as if it were entered in a cell

"11+5" and "=11+5" will produce the same result

Excel Formula – Identify Special Characters in a Cell

No code? But it's so short and easy and beautiful and... :(

Your RegEx pattern [^A-Za-z0-9_-] is used to remove all special characters in all cells.

Sub RegExReplace()

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True

    RegEx.Pattern = "[^A-Za-z0-9_-]"
    For Each objCell In ActiveSheet.UsedRange.Cells
        objCell.Value = RegEx.Replace(objCell.Value, "")
    Next

End Sub

Edit

This is as close as I can get to your original question.

enter image description here

The second code is a user-defined function =RegExCheck(A1,"[^A-Za-z0-9_-]") with 2 arguments. The first one is the cell to check. The second one is the RegEx pattern to check for. If the pattern matches any of the characters in your cell, it will return 1 otherwise 0.

You can use it like any other normal Excel formula if you first open VBA editor with ALT+F11, insert a new module (!) and paste the code below.

Function RegExCheck(objCell As Range, strPattern As String)

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = strPattern

    If RegEx.Replace(objCell.Value, "") = objCell.Value Then
        RegExCheck = 0
    Else
        RegExCheck = 1
    End If

End Function

For users new to RegEx I'll explain your pattern: [^A-Za-z0-9_-]

[] stands for a group of expressions
^ is a logical NOT
[^ ] Combine them to get a group of signs which should not be included
A-Z matches every character from A to Z (upper case)
a-z matches every character from a to z (lower case)
0-9 matches every digit
_ matches a _
- matches a - (This sign breaks your pattern if it's at the wrong position)

Best Answer

Related Solutions

Excel – Function to Evaluate a String as a Formula

Excel Formula – Identify Special Characters in a Cell

Edit

Related Question