If you use online banking, most banks allow you to download your bank movements or statements in Excel format, however usually the information is in CSV format and may not display in neat columns as we would expect it to.
CSV stands for “Comma Separated Values” and is actually a file type that is often used by websites to provide tabulated information, such as a bank statement, but in a format different programs can open and read, including Excel. In a CSV file, information is stored in rows with commas used to indicate how the data should be broken into columns.
.png)
First of all open the file with Excel. If the file doesn’t automatically open with Excel, refer to our July 2016 issue on how to select Excel as the default program for CSV files. Once it is open in Excel the information may look somewhat like the information shown in the image here.
.png)
Select column A by clicking on the column letter at the top of the column and go to the Data tab. Click the Text to Columns button and make sure the “Delimited” option is selected before clicking the “Next” button. The next step is to select the delimiter for your data, this refers to the character used in the CSV file to separate the columns.
.png)
The most commonly used delimiters are a comma (,) or a semi-colon (;). You should be able to tell which one has been used by looking at the data. Now click the tick box for the right delimiter option and review the “Data preview” window to make sure the data is being split up in the right places.
.png)
If everything looks OK, at this point you can simply click the “Finish” button to complete the process or you can click the Next button and format each column as a number, date or text, depending on what each column contains. Once you click the Finish button the data in the spreadsheet will be separated into columns.
