Microsoft Excel Series: Installment #1: Text To Column
One of the many things I am in charge of here at Top of Mind Networks is transforming data we receive from our clients into a usable format that can be imported into our database. I have been doing this for many years now and in doing so I have become very proficient with Microsoft Excel. I have helped our clients out here and there with Excel related requests; because like us, you too need your client data stored in your various databases from time to time to perform tasks like mail merging to send out a letter or email, submit orders to vendors (like Top of Mind J), or transfer data from one database to another. So I am going to start a new blog series with tips and tricks I have learned along the way that you may find useful.
The first installment of this series will deal with the Text To Column functionality of Excel. This particular example comes from one of our clients trying to give their past client database to us for our Surefire System. All of their data was stored in a program called Avery Design Pro. I think this software is used primarily for creating address labels but that is where this client stored their list. When they exported the list to Excel the formatting was all messed up and each data cell had little squares(?) mixed in with the actual name and address data. So to get the data into a usable format we faced the challenge of getting rid of said squares.
What some of you may or may not know is that Excel reads those “little square” characters as tabs. My guess is her Avery software exported the data into a tab delimited format and Excel was just having a little trouble reading it. Luckily, Excel has a really nifty function called Text To Columns. All we had to do was mouse click on column A to select the entire column. Then in the menu bar, select Data, then Text To Columns. The first screen allows you to pick between Delimited or Fixed Width; in this case we want to select Delimited. On the next screen you will need to select what characters the data is delimited by. In this case, make sure everything is unselected except for Tab. Then click Finish.
And viola; no more squares! Now you would just repeat this for columns B and C and so on until all of the columns look correct.