To expand your understanding of the material covered in the chapter, complete the following assignment. You will be working with the DataGen_Companies sheet in your Ch10_Data_File workbook. As noted before, the DataGen_Companies sheet contains a set of “dummy” (plausible, but not real) data about companies generated at https://www.generatedata.com/ that the author of this chapter intentionally injected with common errors seen in data in order to unfold and process it for the sake of practicing Excel functions for the Chapter Practice section. Our goal is to clean and restructure that data using functions and features discussed earlier in this chapter.

1. Open the Ch10_Data_File workbook and examine the data in the DataGen_Companies sheet.
   – What issues do you see with this data?
   – What is present, what is missing: what do we need to delete and what do we need to add?
   – Currently, all our data is in a single cell for each company. We want to have the company name in one column, their street address in another, their city, their ZIP code in others. Altogether we wish to have the data chopped up into segments that correspond with how we may want to use them in the future and align with categories generally associated with mailing addresses.
2. Highlight column A, where all your data is, then go to the Text to Column feature under Data > Data Tools on the ribbon.
3. The Convert Text to Columns Wizard pops up and will guide you through the process of converting a single cell into multiple ones based on where commas or any other recurring characters or patterns may be in your data. Each category in your data (company name, street address, city, ZIP code) is separated from one another using a comma. Click the Delimited checkbox, then Click next.
4. On Step 2 of 3 of the wizard, you are asked to select the delimited present in your data Excel can use to process the conversion. Your text has a comma in between the categories you want to display in individual cells, so select the Comma option by checking the box next to that option. The data preview will show vertical lines where your columns will be inserted (Figure 10.4.1). Click next after confirming that the text would convert as you like.
   

   

5. Your data will now display over 6 columns instead of one, with company names in column A and with & in column F. Even though we used the feature correctly, our conversion is not perfect because of the different types of addresses present. Some businesses have a street or apartment number, some have a P.O. Box number in a cell preceding their street address. Businesses with a P.O. Box number have one more cell’s worth of data than others (Figure 10.4.2).
   

   

6. Let us consolidate the addresses into a single cell for the sake of consistency. Sort column B in Ascending Order to have all the street, apartment and P.O.Box addresses below one another by type.
7. Select the range that contains the P.O. Box numbers. Insert cells to Shift Cells Right.
8. In the blank range, use CONCATENATE to merge the P.O. Box numbers with the street address.
9. Move cell contents to ensure that the City and Zip codes are in the adjacent range without blanks in between.
10. Sort your data to resolve issues with street names with periods or other issues you may see with the data set.
11. Delete Column E with the superfluous & symbol.
12. Save your work for your records.

ATTRIBUTION

Chapter by Emese Felvégi.  CC BY-NC-SA 3.0. Dummy data set from https://mockaroo.com archived here for educational purposes.