Tom’s Tutorials for Excel: TRIM and CLEAN your data

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
8 comments on “Tom’s Tutorials for Excel: TRIM and CLEAN your data
  1. Patrick M says:

    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!

    • DanaH says:

      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

  2. sandeep kothari says:

    Dear Tom,
    =CHAR(0) returns a #VALUE error. How to see ASCII char. 0?

    • Tom Urtis says:

      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.

  3. sandeep kothari says:

    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.

  4. DanaH says:

    Tanto Grazie, Gaspar!

1 Pings/Trackbacks for "Tom’s Tutorials for Excel: TRIM and CLEAN your data"
  1. […] gaspart on 22/08/2011 A futura memoria, segnalo questo dritta di Excel per pulire rapidamente una cella da spazi di troppo e caratteri non stampabili: Excel […]

Leave a Reply to sandeep kothari Cancel reply

Your email address will not be published. Required fields are marked *

*