To separate the contents of a cell into separate columns use the “Text to Columns” wizard in Excel.
You can separate a column of names into first name and last name columns. If there are multiple subjects in a single column you can create a column for each subject. You can choose how to split it up; fixed width or split at each comma, period, semicolon, space, tab or other character.
- Insert blank columns to the right of the column you wish to convert. These columns will hold the converted data.
- Select the column (data range) to convert.
- In the Data tab > Data Tools group select “Text to Columns“.
- Selected delimited or fixed width and select Next.
- Select the delimiters or specify the width of each field. Press finish.
Example: The sales file from Lighting Source; a book distributor, has a column named: Market. Sample values are: Market – United States, Market – France, Market – Germany, Market – Mexico. To analyze sales by territory you need a column with the country name.
The text to columns wizard; using a delimiter of “-“, will create a column with just the country name. The resulting country column will have a leading space character. To remove the leading space insert a new column and use the Formulas > Text > “TRIM(text)” formula. Trim(text) removes all spaces from a text string except for single spaces between words.