Excel Formulas and Tutorial

TEXT TO COLUMNS

There are situations where we can have raw data located in one column of Excel file.

	A	B
1	X01BOLT5	data
2	X02NUT5	data

Generally it is a result of importing from text editing programs, e-mail, or just a raw text. If we want to run queries, use formulas, perform sorting and so on we need to present our data in the form of rows and columns, i.e. convert text to columns.

That's how we want our data to look like:

	A	B	C	D
1	X01	BOLT	5	data
2	X02	NUT	5	data

Note:

Remember to add extra columns if you have data to the right of the converting column. Otherwise it will overwrite existing information. In our case we have inserted two additional columns (B and C) to perform our split.

Inserting additional columns:
(right click, choose insert)

Done:

1. Select the required column (or range of cells)

2. On the Data menu click Text to Columns

3. Choose Delimited or Fixed Width

Text to columns -- Choose Delimited or Fixed Column Width

Note:
Choose 'Delimited' if you have data divided by dot, comma, semicolon, blank or any other symbol.
There are no dividers in our case so we should choose ‘Fixed Width’.

4. Set field widths by creating break lines. Left-click on the screen where you want to create a break line and double click a break line to remove one.

5. In Step 3 you can select column format and destination to put your converted data, but this is optional.

6. Press "Finish". Get the result.

	A	B	C	D
1	X01	BOLT	5	data
2	X02	NUT5		data

Now we have our text converted to columns.

Oops.. there is a problem with a divider. We can see “NUT5” in the last row. It was tricky to set a break line to correctly separate data in fields B and C.

We have to add extra spaces between data to fix it, either manually or with some automation.

Going back to add extra spaces:

Select the column and choose function Find and Replace (press Ctrl+F).

Replace symbol "t" with "t{space}{space}”
(we have added two spaces after "t").

Press ‘Replace All’.

Done:

Now it is much easier to set a break line.

Finally done:

	A	B	C	D
1	X01	BOLT	5	data
2	X02	NUT	5	data

home > text to columns