• Technology
  • CFO.com | US

Spreadsheets: Split Columns at Each Space

When accounting software combines columns during export, use this shortcut to separate the data and put it back in its original form.


Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Grace B. wins a copy of Learn Excel 97-2007 from CFO and MrExcel for her question: “Our accounting software combines columns when it exports to Excel. Is there any shortcut that I can use on this combined data — separated by a space in one column — and split them into their three separate columns again?”

Fig. 1

MrExcelJune1-377

 

I am assuming that there are additional data columns to the right of the column. If you believe that your field will break into three fields, you should insert four or five columns to the right of your column just to be safe. You can delete the extra columns later. Then follow these steps:

Select the data in column A (see Figure 1) by selecting cell A1 and pressing Ctrl+Shift+DownArrow.

From the menu, choose Data, Text to Columns. In Step 1 of the Wizard, choose Delimited (see Figure 2). This means that one specific character (a space) will be used to mark where the field should be broken apart.

Fig 2.

MrExcelJune1-378

 

Click Next.

In Step 3, choose Space as the Delimiter. Uncheck the other delimiters (such as Tab, which is the usual default). (See Figure 3.)

Fig. 3

MrExcelJune1-379

 

Click Next.

Do you have any columns that need to keep the leading zero or leading spaces? If so, click on those columns and change the type from General to Text (see Figure 4). Be aware that choosing Text introduces many problems later, so only choose it if you need to keep the leading zeroes.

Fig. 4

MrExcelJune1-380

 

Click Finish. You will find that your data has been split into three columns.

The main problem that can happen now is that one of the fields had a space in it already. This would cause the data to be split into four columns instead of three. To test, go to the first column that should be completely blank. (In Figure 5, it is column D.) Choose cell D1.

Fig. 5

MrExcelJune1-381

 

Press Ctrl+Shift+DownArrow. If column D is completely blank, you will end up at row 1,048,576. If the cell pointer stops at any other row, you need to manually fix those records.

CFO contributor Bill Jelen is the author of 32 books about Microsoft Excel, including Excel Gurus Gone Wild. You have the chance to win a copy of one of his books by posting a question to the Community Center on the right. If Bill selects your question as the topic of a future column, we’ll send you a book as a thank-you. Bill’s next MrExcel Webcast, “PowerExcel,” is Tuesday, June 14, at 2:00 Eastern Time. Click here for more details as they become available.

 

 

Discuss

Your email address will not be published. Required fields are marked *