How to Convert Text to Columns in Excel
You already know Excel is a great spreadsheet program, but did you know it is also a powerful text editor? Familiarize yourself with a few simple tricks so that you can edit text and use it in your Excel spreadsheets or share it with other programs.
Things You'll Need
- Microsoft Excel, any version (installed)
Launch Excel and open your spreadsheet. Highlight the column containing the data that you want to convert. For instance, if you have a column that contains a list of names in “first name, last name” format, you can convert that text to two columns, one containing the first names and the other containing the last names. To get started, just highlight the column with the list of names.
Click on the “Data” menu and choose the “Text to Columns” option. The next screen allows you to choose either fixed width or delimited format. Choose "fixed width" if every record is the same length; otherwise, choose the "delimited" option. You would choose the latter option for a list of names because the first and last names are separated by a space. That space becomes the delimiter, with everything before the space being placed in one column and everything after it being placed in a second column.
Select the delimiter for a delimited file in the next dialog box or mark the columns if you want to create a fixed-width format. Click “Next” to continue.
Review the next screen that appears and confirm your choices. If everything looks right, click “Finish.” Your spreadsheet will be reformatted and the selected text will be displayed in columns. In the example, the last names will be formatted and displayed in a second column. You can now use that data, transfer it to other programs and work with it more effectively.