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:
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:
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.