Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Getting Rid of Non-Printing Characters Intelligently.
Written by Allen Wyatt (last updated April 30, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
If you work with files that originate from a non-Excel source, you can sometimes end up with characters in your cells that Excel doesn't know how to display properly. For instance, you may have a comma-delimited text file generated by your company's accounting software, and you load the file into Excel. In some cells you may notice that there are small boxes. These represent non-printing characters. Excel displays the small boxes so that you know the character is there, even though it cannot be displayed or printed.
To get rid of these characters you can try to use the Find and Replace feature of Excel. Try these steps:
Figure 1. The Replace tab of the Find and Replace dialog box.
This approach may or may not work, depending mostly on Excel and whether it let you actually copy the offending character in step 1. If it does work, then you have learned a valuable technique for getting rid of the bad characters. If it doesn't work, then you should try a different approach.
One thing to try is to use Word in your "clean up" operations. Copy the data from Excel to a Word document (paste it as regular text), and then replace the offending characters. You can then paste the data back into Excel. Some people report that they get exactly the results they want by using this round-trip approach to working with the data.
You can, of course, use a macro to get rid of the offending characters. It isn't too difficult to create your own version of the CLEAN worksheet function that, instead of simply removing non-printing characters, replaces them with spaces. Consider the following example macro:
Function ReplaceClean(sText As String, Optional sSubText As String = " ") Dim J As Integer Dim vAddText vAddText = Array(Chr(129), Chr(141), Chr(143), Chr(144), Chr(157)) For J = 1 To 31 sText = Replace(sText, Chr(J), sSubText) Next For J = 0 To UBound(vAddText) sText = Replace(sText, vAddText(J), sSubText) Next ReplaceClean = sText End Function
You use this function in the following manner within your worksheet:
=ReplaceClean(B14)
In this case, all non-printing characters in cell B14 are replaced with a space. If you want the characters replaced with something else, just provide the text to replace with. The following example replaces the non-printing characters with a dash:
=ReplaceClean(A1,"-")
The following usage simply removes the non-printing characters, the same as the CLEAN function:
=ReplaceClean(A1,"")
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6126) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Getting Rid of Non-Printing Characters Intelligently.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Named ranges can make it easier to refer to ranges of cells in an understandable way. If you want to delete named ranges ...
Discover MoreNeed to enter a check mark into a cell? There are a number of ways you can get the desired character, depending on the ...
Discover MoreNeed to quickly select a range of cells? Perhaps the easiest way is to use both the mouse and the keyboard together, as ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-05-03 13:35:02
J. Woolley
Re. the TextClean macro described in my earliest comment below, I forgot to mention that formatting of individual characters among text constants in the selected range remains undisturbed even if some characters are removed by the macro. Here is an abbreviated stand-alone version that retains support for Undo (Ctrl+Z):
Public Sub TextClean()
Text_Proc "TextClean"
End Sub
Private Sub Text_Proc(Optional Proc As String = "Undo")
Const myName As String = "Text_Proc"
Static rText As Range, sSave() As String 'Static
Dim rCell As Range, sText As String, sChar As String, sConv As String
Dim sCase As String, sXtra As String, bUndo As Boolean, nSave As Long
Dim nChar As Integer, nConv As Integer, n As Integer
bUndo = (Proc = "Undo")
If bUndo Then
If rText Is Nothing Then Exit Sub
rText.Select
rText.Cells(1).Show
DoEvents
Else
On Error Resume Next
'Intersect is necessary in case Selection.Cells.Count = 1
Set rCell = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0
If rCell Is Nothing Then
sText = "There are no text constants in Selection"
MsgBox sText, vbCritical, Proc
Exit Sub
End If
Set rText = rCell
rText.Select
rText.Cells(1).Show
sText = "ASCII characters 0 thru 31 (Tab, CR, LF, ...) will be " _
& "removed from the selected text. Enter additional " _
& "characters to remove (case-sensitive) including Alt+nnnn " _
& "for UNICHAR(nnnn). ASCII space will be ignored. " _
& "Click Cancel to skip additional characters."
sXtra = InputBox(sText, Proc)
sXtra = Replace(sXtra, " ", "")
ReDim sSave(1 To rText.Cells.Count)
End If
nSave = 0
For Each rCell In rText
With rCell
nSave = nSave + 1
If bUndo Then
sText = sSave(nSave)
sConv = .Value
Else
sText = .Value
sSave(nSave) = sText
sConv = Application.WorksheetFunction.Clean(sText)
For n = 1 To Len(sXtra)
sConv = Replace(sConv, Mid(sXtra, n, 1), "")
Next n
End If
nChar = 1
nConv = 1
For n = 1 To Len(sText)
sChar = Mid(sText, n, 1)
sCase = Mid(sConv, nConv, 1)
If sChar = sCase Then
nConv = nConv + 1
nChar = nChar + 1
ElseIf bUndo Then
.Characters(nChar, 0).Insert sChar
nChar = nChar + 1
Else
.Characters(nChar, 1).Delete
End If
Next n
End With
Next rCell
If bUndo Then Exit Sub
Application.OnUndo ("Undo " & Proc), (ThisWorkbook.Name & "!" & myName)
End Sub
2024-05-02 17:53:12
J. Woolley
@Mike J
Thank you for your question. It depends how you implement My Excel Toolbox.
1. The easiest way is to download the add-in file MyToolbox.xlam from https://sites.google.com/view/MyExcelToolbox and install it as described in the last two paragraphs of the Description section on the Home page. In that case macros can be run by pressing Ctrl+M (not Ctrl+Shift+M) to open the macro named MyToolboxMacros.
2. If you only want to implement the M_Text module containing the TextClean macro, you will discover it depends on other modules in My Excel Toolbox. M_Text requires M_Support and M_Miscellaneous; the latter requires M_WinAPI, F_Lightbox, F_MyToolboxMacros, F_MyToolboxShortcuts, F_PasteSpecial, and F_ProgressBar. Therefore, you must insert those four standard modules plus five user form modules into your VBA project.
3. If you only want to implement the TextClean macro, you need to copy/paste TextClean, Text_Proc, Title, ProperX, and ProperY from M_Text plus Selection_OK from M_Miscellaneous.
If you have further questions about My Excel Toolbox, please use the Comment page at https://sites.google.com/view/MyExcelToolbox
2024-05-02 07:38:07
Mike J
@J.Woolley
Have I implemented your MyToolbox incorrectly?
I am trying to run TextClean() from VBE, using F5 after selecting a range, but I am getting "Compile error: Variable not defined". It seems to be xlErrSpill in Public Function ErrorAsText() in a different module. If I comment out the Case xlErrSpill test, the problem repeats for xlErrCalc. After commenting out this one the macro works perfectly.
It seems it is because I am using excel 2010, but commenting out these 2 case statements probably means it may not function correctly in later versions. How can I get it to work with v2010 and also v2021, which I am about to use on another computer?
2024-05-01 16:44:28
J. Woolley
The TextClean macro in My Excel Toolbox removes ASCII characters 0 through 31 (Tab, CR, LF, etc.) from text constants in the selected range of cells. Additional characters can be specified for removal (case-sensitive) including Alt+nnnn for UNICHAR(nnnn). The macro supports Undo (Ctrl+Z).
See https://sites.google.com/view/MyExcelToolbox/
2024-04-30 08:10:29
Mark E Watson
Hello,
Again, if the user is routinely receiving file in a defined form (such as CSV) I suggest that they build a Power Query to do the incoming data cleaning. That's exactly what Power Query is designed to do.
Thanks,
Mark
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments