A feature of Microsoft Excel ‘text to columns’ option under the Data menu allows information in a cell or range of cells to be split into different columns. We find this tends to occur on exported lists of address information.
Below is the data in column B which is to be split in two different columns:
Following are the steps to split data into different columns using ‘Text to columns’ :
Select the range of data you need to work at.
Select Data from menu and select Text to
Columns
in the Data Tools group.
Check Delimited in the dialog box (1 of 3) and click Next.
In Step 2 of 3, check the box related to the specific separator in the information.
If the separator is tab, check tab or if the separator
is semicolon, check semicolon and so on.
If the separator is other than the specified check boxes given in the wizard, check Other box and mention the separator in the box for Other:
Select Other in the dialog box (2 of 3) and enter the delimiter used in your data (-) in the box for Other: and click Next.
Enter the Destination (C3) to paste the data after splitting and Click Finish.
Click Ok in the dialogue box.
Shown below is the output:
—————————————————————————————————————————————————-
Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoices and Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.
Excel 2010 version
Data before removing duplicates
Data after removing duplicates
