Manipulating and Massaging Data in Excel and VBA

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Chad Rothschiller, a program manager on the Excel team has written a lengthy article discussing using formulas to 'clean up' data in Excel. It is very indepth and covers these topics

  • Import the data and don’t accidentally drop the leading zeros - explains how to use the import wizard
  • Formatting numbers and dates in columns correctly
  • Fixing up names - upper and lower case, extra spaces, converting middle names to middle initials etc
  • Lower case email addresses
  • Formatting phone numbers and inserting hyphens
  • Sorting the table
  • Cleaning text using TRIM, CLEAN and SUBSTITUTE functions


This is the sort of work we are often asked to automate using VBA code. If you regularly need to pull data from text files or from databases and then have to spend time cleaning them up so you can use them in reports or charts then this is a task that can be performed easily by a macro. This can save hours of time each time this task is performed and can really increase your productivity.

Contact us to see how we can provide you with a quote for this sort of work that can show your boss how much time you will save as a result..