Microsoft Excel Tips

Est. 1998

Microsoft Excel Tips

Business, Science and Engineering

Contact EMAGENIT Newsletter

Interesting Microsoft Excel tip and problem solving.

Presented below is our Microsoft Excel tip that will save you time and effort in your everyday Excel activities.

How to Clean Worksheet Text Data with Formulas

Many times when text data is downloaded, it comes in corrupted. Bank and financial downloads are notorious for this, engineering and science data sometimes. The corruption generally comes in the form of extra spaces before, in between and after the text; non-printable ascii characters in the text; and unwanted characters in the text. If your text data includes these imperfections, your lookup and logic formulas; Excel filter tools; PivotTables and so forth will not interpret the data correctly. To clean your data, the new buzzword is "shape", insert a column next to your text data and use the worksheet functions CLEAN, TRIM and SUBSTITUTE to remove and/or replace unwanted characters from the text. The CLEAN function removes non-printable ASCII characters, the scourge of data processing. Non-printables are not generally seen and  the ones most encountered occupy the number set 0-31 on the ASCII table (lookup on internet). Just when you assume you do not have any of them, you do, so be alert. The CLEAN function removes them. The TRIM function removes leading and trailing edge spaces from the text data and makes the spacing between words in the text data 1 space. You use that function if you have multi-word text and you want to keep the spacing between the words. The SUBSTITUTE function can be used to remove and replace unwanted characters in text data with new characters (if replacing alphabetical characters, case sensitive). The author likes to use SUBSTITUTE to remove all the blank spaces in text when required. You search for a space and replace it with an empty string which is two "" quotes put together. The formulas that use these functions are presented below in the two data sets below.

Cleaning Your Data with TRIM, CLEAN and SUBSTITUTE

Use the CLEAN, TRIM and SUBSTITUTE functions to clean your data. Insert new column next to text data to put formulas in.

When done cleaning the data, copy/paste it back over the original data using Copy/PasteSpecial/Values. Remember to delete the extra column that was made for the formulas. Also be sure to check out all the TEXT worksheet functions like LEFT, RIGHT, MID, SEARCH, and the much overlooked TEXT function which solves how to get the month name and day name from a date format value (think Number tab on Format Cells dialog box as a function).

Copy/Paste/Special Data

Copy/paste the clean text column to the original text column and use PasteSpecial/Values.

Contact EMAGENIT Newsletter
Need Help? Please call us at 1.805.498.7162

Microsoft Excel Products

- Excel Self-Study Manuals

Copyright © 2002-2020

EMAGENIT All Rights Reserved