Excel pro tips: Importing and parsing data
Image: Rob Schultz
Data imported from other spreadsheets or databases is already separated into fields, using something called a field delimiter—a comma, tab, space, or custom character—to separate one field from another. These databases import easily into Excel and place all the fields in separate columns. If your company pays bills and/or banks online, these sites usually offer copies of the company’s records in electronic form. CSV (comma separated values) is the most common data exchange format and, if offered, the best one to use. But what happens when all the data imports into one cell?
Importing & parsing data
If you copy a block of data from a webpage, a word processing file, or other text file, then paste into Excel using the Paste > Special > Text command, all the data is dumped into a column of single cells. This means the records are copied into separate rows, but all of the fields are in one cell. What a mess! Now what?
Note: it looks like the data spills over into the adjacent columns, but only the first column contains the pasted information.
2. Select Data > Text to Columns. In the Convert Text to Columns Wizard dialog window, choose the Delimited button, then click Next.
3. In the next dialog window (Step 2 of 3, below) select the delimiter—that is, the character that separates the fields. In CSV files, it’s a comma. Some databases use a tab, some use a semicolon. The only character that separates the data in this database is a space, so check the Space box.
4. Check the box that says: Treat consecutive delimiters as one, in case multiple spaces occur in one string of text. This setting also applies if your data has a delimiter of more than one character between data fields, or if the data contains multiple custom delimiters.
5. From the Text qualifier dropdown list, select None, because the records are not enclosed with single or double quotes.
6. And last, click Next.
7. In the next dialog window (Step 3 of 3, below), Excel gives you the option to change the format of each column. The first column is selected by default. Notice the header says ‘General.’ General works for all numeric fields. Because this is a text field, click the Text button to change this format.
8. Click anywhere in the second column, and the highlight moves to that column. If the selected format of General is incorrect, click the correct format for that column from the options above. In this case, Text is correct format.
9. Repeat this process through all the imported fields. Then click the Finish button.
10. Now the boss wants the months separated. First, insert a column beside the Modern Months column so the parsed data doesn’t overwrite the information in column G.
11. Move your cursor to column G. Select Insert > Insert Sheet Column.
12. Highlight the second field “Modern Months” in column F, and follow the instructions above to separate this one field into two fields. Note that the custom delimiter is the slash key.
13. Change the columns from General to Text and click Finish. Now the months are in two columns instead of one.
Use Text functions to polish the data
1. First, let’s replace the word “time” with the word “month.” Press Ctrl+ H. Click the Replace tab. Type time in the Find What search box and month in the Replace With search box, then click the Replace All button.
2. Now, let’s join columns H and I into one field. Move your cursor to column J and enter the following text formula: =CONCATENATE(H3, “ “, I 3). Note there’s a space between the quotation marks, so Excel places a space between the two words. Copy this formula from J3 down through J14.
3. For the next step, you need to delete the two columns H and I so the spreadsheet only shows the concatenated column J. But if you delete columns H and I, then column J disappears, because it’s expressing a formula based on the prior two columns. You have to convert J from a formula to a text value.
4. Select J3:J14. Type Ctrl+ C (for Copy), move to column K3, right-click, and select Paste Special. In the Paste Special dialog box, choose Values and click OK.
5. Now you can delete columns H, I, and J, because column K has the completed text string.
Note: In Excel 2016, there’s a new text function called CONCAT. According to Excel, it’s like CONCATENATE, but better, because it’s shorter, easier to type, and supports range references as opposed to just cell references. This means you can join the contents of several ranges: For example A3:C3, “ “, D3:F3 would combine the contents of cells A3, B3, and C3, to cells D3, E3, and F3 with a space between the two ranges.
6. In 2016, CONCATENATE is not shown on the dropdown list of Text Functions (under Formulas > Text). If you prefer this function over the CONCAT function, look for it under Formulas > More Functions > Compatibility. Selecting functions from the menus provides access to the Wizard dialogs that coach you through the syntax of each formula.
We’ll leave you with one last, great tip: The fastest and easiest method for joining the data in two or more cells is to use the ampersand. Yes, this little symbol works just as efficiently as the CONCAT and CONCATENATE functions. Just enter the formula like this: =A3& “ “ &B3. This simple formula joins the contents of Cell A3 with the contents of cell B3, with a space between the two.
