Text to columns in Excel

Microsoft Excel is widely used in startups to large companies. The data are entered in required rows and columns, and sometimes the data entered in one cell needs to enter in a separate column. For example, if a person enters a name along with a surname, the user wants to split the name and surname and intends to enter the surname in a separate column.

Typing manually is a time-consuming process and may sometimes lead to manual errors. "Convert Text to Column Wizard "is used to solve this issue. To split the data from the cell, there are two methods used. Using a delimiter is one of the options, or the users are allowed to fix the width in an adjacent column for the text.

Let's look at changing the text to columns in a step-by-step method.

Splitting the data in a separate column

In the given data name of the person and their car are entered in a set of columns. The user needs to split the car names and get these into separate column. To split the last name from the cell, following steps are followed.

STEP 1: Enter the required data in a spreadsheet.

Here names of the person and the car they are using are entered in the worksheet, separated using a comma.

STEP 2: Select the required data range to display the content in a new column. Then choose Data> Data Tool Group>Text to Columns.

STEP 3:  A dialogue box will appear. In the delimiter, there present various options. Choose a comma as a delimiter. Click Apply.

STEP 4: Destination of the cell is selected before.  If the destination cell is not selected properly, it will overwrite the existing data set in the first column and last name in the adjacent column.

STEP 5: The car names are in separate columns, as shown below.

In the above process, the delimiters option is applied as Comma. Several other options are present, such as Semicolon, Customized, Space and Tab. Users can also use this option; a preview of how this option works is also seen in the dialogue box.

How to split First name and Last name in the cell?

To split the first name and last name in the cell, following steps are followed.

STEP 1: Enter and select the data in the spreadsheet.

STEP 2: Click “ALT+A+E”. A dialog box regarding “Convert Text to Columns Wizard” will open.

STEP 3: In the dialog box, check whether delimited is selected, and click “Next”.

STEP 4: In the Next step, a dialog box appears, in that uncheck the “Tab” option and choose “Space” as a Delimiter. Then click on “Treat Consecutive delimiters as one” option. Click on “Next”

STEP 5: In the wizard choose the destination where you want to display the result. Here cell b1 is selected, else the new data overwrites the existing data.  Click Finish after choosing the destination.

STEP 6: This option will split the data where first name and last name in a separate column as shown below.

The above technique is used only for splitting first names and last names. For splitting multiple data in a single column different method is implemented.

How to split the data in a single cell into multiple adjacent cells

Sometimes excel contains the whole data in a single set of column where there is a need to split the data into four adjacent columns. To implement this technique following steps are used.

STEP 1: Enter the data in the spreadsheet.

STEP 2: Select the data, choose Data> Data Tools> Text to columns.

STEP 3: In the dialog box choose, fixed width as an option. Click Next.

STEP 4: In the data preview section, a vertical line mark called break line which allows the user to adjust it as per the data structure. Click Next after the data structure is fixed.

STEP 5: In the dialog box enter the new destination, else the existing data overwrites the new data. After the destination is selected, click Finish as shown below.

STEP 6: The data is split based on Hex No and Description, where the new data is entered in the new column as shown below.

How to convert Date to Text using Text to Column Option

To convert date to text, either by using formula method or by Text to column option.

STEP 1: Enter the data in the spreadsheet.

STEP 2: To convert text to columns, choose Data> Text to columns. A dialog box appears in that choose delimiter as an option and click Next.

Step 3: A pop-up box appears, uncheck all the boxes, and click Next.

STEP 4: A dialog box appears, in that choose Text as an option and choose the destination and Click Finish.

STEP 5: The result is displayed as shown below, where the date is converted into text.

Splitting the several characters from the Cell

To split the several characters from the cell, following methods are followed.

STEP 1: Enter the data in the spreadsheet.

STEP 2: Click “ALT+A+E” for fixed width and choose Next.

STEP 3: Adjust the data which is away from nine characters from the starting point which is called delimiter as shown below. Click Next.

STEP 4: A dialog box appears where in that choose, date and select the destination where the result wants to display.

STEP 5: The result will be displayed as shown below, where the first nine data are split in a separate column and the rest of the data are inserted in separate column.

STEP 6: The result will be displayed as shown above where the first nine data are inserted in a separate column and other data are inserted in separate columns.

Summary

In the above tutorial, the step-by-step process of how to convert text to a new column is discussed. This methodology is a quick one to implement rather than typing manually.