Text to Columns the easiest way to add data to an excel worksheet is by opening a file directly in excel. Say each month you get a report electronically, and each month you want to layer the next report onto the report with the prior month’s reports. In month one you would open the original file in excel save and you would be done. In month two you would open month two’s file and copy and paste the data directly into next empty rows in the spread containing month one. So each month you save in the same file one right after the other. So rows 1-100 are January, 101-200 are February… etc.

This is quite basic and may seem easy to you if your familiar with opening files in excel. The monkey wrench arises when you have a file that’s in a different format than excel. Usually CSV “Comma Separated Value” file or a plain text (.txt) file. These files usually need a little manipulation to open in the most usable format. This is where text to columns comes in very handy. Text to columns allows us to parse data in many ways. (Divide a string of data into smaller parts in multiple cells). It allows us to parse the data by choosing a fixed width (if every row of data is equal and the breaks would always be the same) or we can delimit our data by various factors (split the data at every comma, semicolon, period space or any character that you need to delimit the data)

Let’s look at some examples in the video above.

DATE allows you to have the month, day and year is separate columns and then concatenate the date into the date format that you choose. If you have a situation where you are given data with the months, days and years in separate columns (or if you parsed the dates by month, day and year to evaluate or change the data) we‘ll use the DATE formula to put the month, day and year into the date format of our choosing. Syntax: DATE(year, month, day)

Filtering data is a very useful tool. You select your range of data and then click the filter icon in the Sort & Filter dropdown. Filtering will put dropdown arrows on ever column giving you access to select a value from any column really quick. You can select one value or multiple values, using the checkboxes next to each value (this multiple selection feature is nice when you want to see YTD values, you can select say January – June to see the see the June YTD). The other great feature about filtering data is when you click on the drop down on the top of one of the columns you can select filter by colors or number filters. Using number filters you can choose to filter number greater than x, or less than y, or in between x and y or equals. You have a lot of control over the criteria that data is displayed.

Trim – Removes all spaces from text except for single spaces between words. This is good for data that contains irregular spacing. Let’s say that our accounts receivable system exports our invoicing information with extra spacing. It was probably programmed to hold spaces for future (larger) invoice numbers. Got to be optimistic 😉

 

CONCATENATE joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, a space, a symbol or a combination of those items.

For example, if your worksheet contains a first name in column A and the last names in column B, you can combine the two values in another cell by using concatenate