Tom’s Tutorial For Excel: TRIM and CLEAN your data
Sometimes you will receive output files from data warehouse applications, or you will copy data from a web page onto your Excel worksheet. Many cells can look jumbled with the data they hold, because of extra (and usually invisible) spacebar or ASCII characters that were not meant to be a part of the actual data.
Two functions named TRIM and CLEAN work well together to return a list of such data into a more Excel-friendly format. You can see the formula bar in the picture that with the original example data in cell A1, the formula in cell B1 is
=TRIM(CLEAN(A1))
The TRIM function corrects irregular spacing by removing all spaces from text except for single spaces between words.
The nested CLEAN function in the formula removes all non-printable characters from the text.
These two functions do not correct everything; there are some characters you’ll encounter that require other tactics to eradicate. But when it comes to excess spacebar characters (ASCII #32), TRIM can usually do the job, and CLEAN handles the eradication of ASCII characters 0 to 31.
To see a list of ASCII characters, in a fresh worksheet, select cell A1, enter the formula
=CHAR(ROW())
and copy it down to cell A255.
The characters you see in cells A1:A32 will be rid by TRIM and CLEAN. At this point in typical practice, you’d copy column B and paste special for values to yield a more user-friendly list. Finally, delete column A and you are good to go.
The one embellishment I would add for CLEAN is that it will not clear ANSI 160, the “non-breaking space”. It often rears its ugly head when you copy data from a web browser or another app and then paste it into Excel.
Thus, I often go with:
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),” “)))
Cheers!
Hi Patrick, thanks. So true that 160 is a pain, quite common as you said, and should be deleted wherever it’s found. I stopped at ascii #32 and I should have mentioned 160 because they resemble 32 in appearance. I deal with 160 differently than for 32 because as the spacebar character, the spaces between words need to be kept.
Sometimes when 160 is an issue, it’s the only issue. As an alternative to the SUBSTITUTE function, what I do to eradicate all 160’s on the sheet wherever they may be is, hit Alt+F11, Ctrl+G, type
ActiveSheet.UsedRange.Replace what:=Chr(160), Replacement:=""and hit Enter. Then it’s Alt+Q to return to the now 160-less worksheet.
Thanks again for the comment, good to hear from you! Tom
Dear Tom,
=CHAR(0) returns a #VALUE error. How to see ASCII char. 0?
There is no such ascii character as index 0.
To see all 255 ascii characters, open a new workbook.
Enter this formula in cell A1:
=CHAR(ROW())
Copy that down to cell A255 and you will see the ascii characters with their row numbers as their ascii numbers.
Dear Tom
ActiveCell.Replace what:=Chr(160), Replacement:=”” (as suggested by DanaH in comment above) is not working. It is not removing the non-print. char.
Pl help.
Whatever character you want to replace might not be 160. Use the CODE function to find out what that specific character really is that you want to replace.
Thanks Tom.
Tanto Grazie, Gaspar!