Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006


OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal












Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com



  Tuesday, November 11, 2014 – Permalink –

Chiropractics for Excel

Knead and pound numbers

Chad Rothschiller, a program manager on the Excel team, discusses using formulas to 'clean up' data in Excel.


Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another.

This example considers a sample data set and walks through the steps to clean up the data and perform various transformations on the data set to massage it into a more desirable format.


I'm sure you've been faced with at least one of theses problems:

  • Import the data and don't accidentally drop the leading zeros!

  • Formatting SSN

  • Inserting hyphens

  • Fixing up names

  • Lower case E-Mail Address

  • Format Home Phone

  • Inserting parenthesis & hyphens

  • Trim extra spaces from Address

  • Add City values to the new table

  • Make all State value upper case

  • Fill in Postal Code

  • Finalize the values
Manipulate and massage  


See all Topics

Labels: , , , , ,


<Doug Klippert@ 3:06 AM

Comments: Post a Comment