Excel Hack

Text to column shortcut

Text to columns is used to split the text contained in a single cell into multiple columns. For example, if you have multiple strings in the same cell, you can split these into cells of two columns. This can be done either by using the delimiter or by splitting with a fixed width. Using shortcuts (access keys), you can process large amounts of data instantly and increase your work productivity.

Text to column shortcut

Windows
Access Key: Alt + A + E

Mac
None

Split by delimiter

Text to columns has the ability to split multiple strings in a cell into multiple column cells using delimiters such as tab, semicolon, comma, and space.

Total Time: 1 minute

Select a cell

Select a cell

Select the cell that will be the starting point of the data.

Select data to the end of the column

Select data to the end of the column

Press Shift + (Down Arrow key) until the last cell of data in the column is selected. Press Alt + A + E.

Select the file type

Select the file type

The Convert Text to Columns Wizard - Step 1 of 3 dialog box will appear. Make sure that Delimited is selected, and press Enter.

Put a check mark in Delimiters

Put a check mark in Delimiters

The Convert Text to Columns Wizard - Step 2 of 3 dialog box will appear. Select Delimiters (ex Comma) and press Enter.

Set the format of the column data

Set the format of the column data

The Convert Text to Columns Wizard - Step 3 of 3 dialog box will appear. Make sure that the format is set to General. To reflect the selected data in multiple columns, enter the cell number of the absolute reference (ex $B$2) that will be the starting point of the column you want to reflect, and press the Enter.

Finish splitting the columns

Finish splitting the columns

The data of multiple strings contained in a single cell could be split into cells of multiple columns each.

Divide by fixed width

If the strings entered in a cell are aligned in the column direction (vertically) and there are spaces between the strings, there is a function called fixed width that splits the multiple strings in a cell into multiple column cells.

Select a cell

Select the cell that will be the starting point of the data.

Select data to the end of the column

Press Shift + (Down Arrow key) until the last cell of data in the column is selected. Press Alt + A + E.

Select Fixed width

The Convert Text to Columns Wizard – Step 1 of 3 dialog box will appear.

Press Shift + ↓ (Down Arrow key) to select Fixed width and press Enter.

Select Next

The Convert Text to Columns Wizard – Step 2 of 3 dialog box will appear.

Press Enter.

Set the format of the column data

The Convert Text to Columns Wizard – Step 3 of 3 dialog box will appear. Make sure that the format is set to General.

To reflect the selected data in multiple columns, enter the cell number of the absolute reference (ex $B$2) that will be the starting point of the column you want to reflect, and press the Enter.

Finish splitting the columns

The data of multiple strings contained in a single cell could be split into cells of multiple columns each.

Comments

Please send us your comments on this article.

0 comments
Inline Feedbacks
View all comments