3

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
4
  • 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 )
    – Bandrami
    Commented May 19, 2014 at 7:55
  • Not a formula, but you can save the spreadsheet as CSV then reopen it. Non ASCII text is converted to ?.
    – Calvin
    Commented May 19, 2014 at 8:10
  • 2
    @Bandrami: You're confusing at least four things there. For starters, there's no such thing as UTF encoding. UTF-8 and UTF-16 exist. Next, the bug you quote is due to an encoding that's neither. The bug is also in Notepad, on file input, not Excel. Finally, once you've loaded a file in Excel, input encodings no longer matter.
    – MSalters
    Commented May 19, 2014 at 9:45
  • You're close, the bug is in Windows. Notepad just doesn't work around it like like most apps do. Determining text encoding by inspection is non-trivial and with the broken non-standard that is UTF 16, impossible.
    – Bandrami
    Commented May 20, 2014 at 8:38

5 Answers 5

1

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
1

Well, here's an approach to the "true ASCII" portion, and one can make a slight adjustment for 255 vs. the 127. It is set for up to ten characters in the cell text being checked, but one can easily make that any length one chooses, though it takes more to run if checking for 20,000 characters in every cell vs. a smaller, likely seeming figure based upon your judgment. Also, it is checking cell A1 with the idea you'd fill a column to match, so if text to check is in A1:A20000, you'd enter the formula in, say, B1:B20000. (I have not looked at whether one could use it in a way that triggered spilling so that it filled the column for you rather than having to copy and paste down.)

=TEXTJOIN("",TRUE,IFERROR(CHAR(IF(UNICODE(MID(A1,ROW(1:10),1))>127,"",UNICODE(MID(A1,ROW(1:10),1)))),""))

Basically, it uses the UNICODE() function (not CODE() since there are characters, maybe, that are not in the <255 range) to read each character in the cell being checked. MID() is used to isolate a character at a time (length 1 for the last parameter) and the way we check EACH character is to use ROW(1:whatever number you choose) to give it more than just a single starting point.

Then we test the results vs. 127, generating "" for any character at all that is not in the true ASCII set. Then we recreate the characters (CHAR(), which we can safely use because there are NO results it cannot handle properly.) for our successes, getting errors for all those that were "". Those errors are handled by IFERROR() which turns them back to "". Finally, TEXTJOIN() joins them together to give the single result we want rather than SPILLING.

The result WILL be text but one can process it further if there are cells that are meant to be numerical by wrapping it in VALUE() to get a real value or an error, and then IFERROR() to give the original text result if `VALUE() failed. So things that could be numbers come out as numbers and the rest as text.

VBA could be used following this approach to remove anything non-ASCII (true or <=255, whichever) if one desired since if nothing else, VBA can always run any formula you can write in a cell. That would make it transparent (no helper column) and VBA can also write results right over the original which is a nice feature for imported (and therefore still available in its original form if something goes wrong) data. Not as nice a feature for entered data that is not available for reloading if something goes wrong...

It can also be readily adapted a couple ways for a list of chosen characters to test for rather than everything >127 or >255. Or one could adapt to leave particular characters in.

0

Dump it to a csv and run it through the unix command cat -v. Non-ASCII characters will be displayed in M- notation, so just grep for M-.

0

I came here searching for the same answer. By viewing the answers, I realize that there is not a simple solution. This is a subroutine that I call in my procedures. You can adapt it to your convenience, the only thing you need is define first (in your own code) the range where you going to apply substitutions. I hope this can be useful for someone.

Public Sub M2_reemplaza_acentos()
    'replace accented characters
    Dim Orig As Variant, Sust As Variant, i As Integer
    Orig = Array("á", "é", "í", "ó", "ú", "ñ", "Á", "É", "Í", "Ó", "Ú", "Ñ", "km/h")
    Sust = Array("a", "e", "i", "o", "u", "n", "A", "E", "I", "O", "U", "N", "kph")
    Application.ScreenUpdating = False
    For i = LBound(Orig) To UBound(Orig)
    ActiveSheet.Cells.Replace _
    What:=Orig(i), _
    Replacement:=Sust(i), _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    MatchCase:=False
    Next i
    Application.ScreenUpdating = True
    End Sub
0

I'm not totally clear what you want, and although you clearly state worksheet function, I offer this VBa code as it's possibly going to be more customizable...

According to ASCII, the is has a decimal value of greater than 127 (actual value doesn't matter)... So, this code checks each character's value and if it's above 127, it flags it... This does mean you'll need to review the link to see which characters are 'OK' characters to use.

Sub Sheet2_Button1_Click()


    Dim rCell As Range
    Dim rRng As Range

    Set rRng = Range("A1:D8")

    For Each rCell In rRng.Cells
        Debug.Print rCell.Address & " --- " & rCell.Value
        Dim s As String
         s = rCell.Value
         
        For i = 1 To Len(s)
        
             Dim c2 As String
             Dim ascInt As Integer
             
             c2 = Mid(s, i, 1)
             ascInt = asc(Mid(s, i, 1))
             
             If (ascInt > 127) Then
                 MsgBox ("Cell " & rCell.Address & " has a " & c2)
             End If
             
        Next i
         
    Next rCell

End Sub

So, as you can see, I've used the 2 examples over a small table. I run the macro, and for each occurrence of the character, it shows up in a message box

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .