Skip to content

Excel: Text to Columns

I work a lot with text files containing data which is, to some degree or another, structured. Whether a breach file from a published breach, or the result of a powershell query such as Get-ADUser, Get-ADComputer, or Get-ADDirectReports, I need to separate the data into columns so that I can work with it. This is where the Text to Columns feature in Excel comes in handy.

Assuming the file I am working from stems from a powershell query, I start export the output to CSV. From there, I open the resulting file in Excel, highlight the column of text, and then go to the Data tab and select Text to Columns:

Screenshot of Excel, the Data tab selected, Data and Text to Columns highlighted in red.

Next, I simply go through the wizard. First, I tell Excel that the data is delimited by a a character, then I define the delimiter as a comma, before hitting finish:

Screenshot from the Excel Convert Text to Columns Wizard, Comma checked off as the delimiter. Comma and Finish highlighted in red.

Once you hit finish, the columns are populated. As an aside, I recommend removing the “#Type” line of the CSV file, to avoid Excel automagically parsing some of the file into columns on its own. It makes for much easier work in Excel.

By posting a comment, you consent to our collecting the information you enter. See privacy policy for more information.

This site uses Akismet to reduce spam. Learn how your comment data is processed.