Connect with us

Excel Tips and Tricks

Excel Tips: How to Split Data into Multiple Columns



There are various reasons accountants might want to export a report from the accounting software to an excel sheet, and perhaps the most typical is so that you can quickly calculate measures such as aCurrent Ratio” or a “Times Interest Earned Ratio”.

Generally, it makes more sense to export a report to an Excel worksheet than to text (TXT) file. Saving a report to a simple text file doesn’t look nearly as slick as choosing any of the Excel options.


Most of accounting software now-days have capability to directly export it into an Excel sheet but, that maybe not the case for some old accounting software, exporting to excel directly may not be available. If that is the case, you have no choice but to export it into a text file, then open it in your Excel sheet.

The Exporting process itself is quick and easy to do, a piece of cake. The real most time consuming process is splitting the text data into multiple columns where you can break up data containing multiple words such as a first name and last name, or city, state, and zip code, into separate columns. Or, any other words that maybe on the description or memo column that you want to use it as a variable on the analysis [in the Excel sheet after the conversion from TXT file into the XLS [or XXLS].

Relax, here I share how. Follow on…

Step-1. If necessary, insert blank columns to the left of the cells you want to convert into multiple columns. If you want your data in three columns, you must have two blank columns.

Step-2. Select the cells you want to convert. You can’t split empty cells, and you can’t split merged cells. You must first unmerge the cells.

Step-3. Choose Data => Data Tools => Text to Columns. The Convert Text to Columns Wizard appears.

Step-4. Select the Original Data type that best suits your existing data. For example, if you’re separating text that is variable in length such as a first name and last name, select Delimited. The Delimited data type works best if your data has a similar format. If all cells contain a specific number of characters, choose Fixed Width. See below figure. If your data type is delimited, be sure each section is separated by a common character such as a comma, period, apostrophe, or tab.

Convert text to multiple columns

Step-5. Click Next. The option you see next depends on which data type you selected in Step 4.

Step-6. If you selected Fixed Width, click the ruler bar where you want the data to split. If you selected Delimited, enter the character you use to separate your text. In the next Figure, the text is separated by a comma.

Splitting data in to multiple columns

Step-7. Click Finish. Excel separates the selected cells into multiple columns.

Step-8. Click OK.

For regular data analyses, you should know already what variables you need and which columns you need to split. In this case, you would be better to post [key in] the data into your accounting system in a manner that will make the splitting process easier.

Example: Let’s assume that you need to obtain the “airwaybill number” as one of your regular Account Receivable analysis variable, but since there is no field to key in the airwaybill number, you then put it into the “Memo” or “Description” field during the data entry process in your accounting system together with description of the merchandise delivered to customers. For easier splitting process, you would need to always put the airwaybill number at the end of every description [memo].

To split data into two lines in the same cell, press Alt + Enter at the point where you want to break the line.

Are you looking for easy accounting tutorial? Established since 2007, hosts more than 1300 articles (still growing), and has helped millions accounting student, teacher, junior accountants and small business owners, worldwide.