Text to Columns

When you import a text file into Excel the Text to Columns Wizard should appear automatically. If this wizard does not appear then change the file extension to ".txt".

microsoft excel docs

It is possible to bypass this wizard by holding down Ctrl when you open the text file.
You can split a column of data into several columns using the (Data > Text to Columns) command.

microsoft excel docs

This wizard allows you to control exactly how exactly how Excel will display a text file
The advantage of this being a wizard and displaying a preview of the data at all three stages means you can simply go back and change the settings if the preview is not how you want the data to be displayed.
You can decide which character or combination of characters to use to delimit the columns as well as the type of the data in each column.
You can also exclude one or more rows at the top of your file.

Fixed Width or Delimited ?

Excel will also try to determine whether the data is delimited or whether it has fixed widths.

microsoft excel docs

If your data has a fixed width then the preview will display a ruler at the top and you can change the widths by dragging the column divide with the mouse.

microsoft excel docs

If your data is fixed-width, be sure to check the data preview window.
You can create a column break by clicking with the left mouse button at the desired position.
You can remove a column break by double clicking on the desired line with the left mouse button.
You can move a column break by dragging it by holding down with the left mouse button.
If you are expanding a block of cells you must select the block of cells first, the current region is not assumed.

Choosing the Delimiter

If your data is delimited (i.e. separated) with a character then you can select which character you want to use to separate the columns.

microsoft excel docs

You can also select more than one check box if your data is delimited by multiple characters.
If multiple characters are selected then a new column will be started where any of the characters occur.

Formatting your columns

This allows to specify how you want the columns to be formatted.

microsoft excel docs

General - Text is left aligned and numbers are right aligned.
Text - All data is treated as text including any numerical data.
Date -


If your data contains numbers that exceed 15 digits then they must be formatted as text.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext