If you have a list of data in a column, formatted in a similar manner throughout that you wish to separate into several columns, you can use Excel’s Text to Columns feature to separate this information.
For example, say your list is in column A and it contains a list of customer email addresses. You want to retrieve just the part before the “@”, because that is also their ID in your system. If their email addresses are set up in a similar format throughout (i.e. first, middle, last initials and a number – 4 characters before the @), you can use the Text to Column’s fixed width option. More likely though, your customer’s email addresses are each set up differently (i.e. various number of characters before the @), so in this case, you can use the delimited option, where you specify a character to specify where to separate each field. In this case the character is the “@” symbol.
To do this, select the column with your list, then Data | Text to Columns in pre-Excel 2007 versions. In Excel 2007, select the Data tab and Text to Columns on the ribbon.
Assuming you are using the delimited option, select the Delimited option, and click Next. Under Delimiters, select Other and enter a @ in the box. Make sure no other boxes are checked here. In the Data preview window, you’ll see a line between the email address’ user name and the domain, where the @ symbol would be. Click Next.
You can change the column data format and specify the destination of the data, if you wish. Note: if you don’t want to import one of the columns, click the column so that the information is shaded black with white text, and select the Do not import column (skip) option under Column data format. Click Finish.
If you happen to have data that is all formatted identically, you can use the fixed width option in the first step, and drag a line in the Data preview window so that it separates your data where you want it.