Text to Column function in Excel

WPS Spreadsheet could be an alternative to Microsoft Office Excel. It includes 100's of built-in formulas, pivot tables, and more. For those who want to split the cell text into multiple columns, the function of Text to Column will help you get what you want. WPS Spreadsheet seems superior to table processor for its Smart Split Columns function.

In today's case, we want to split text with fixed width and delimited text respectively in a smart way.

· Fixed Width Text:

Fixed Width means fields are aligned in columns with spaces between each field.

In this case, we know Student number is composed of state and applicant number and always begins with the abbreviation of state in the U.S.A. (AL: Alabama, CA: California, MA: Massachusetts...)

1. Open your table in WPS Spreadsheet.

2. Select the array A2:A16, copy and paste it to B2:B16, because this is the place we want the divided text to return.

3. Select B2:B16, head to the Data tab, and click Text to Columns > Smart Split Columns > Finish.

A traditional way (the same in Microsoft Excel) to split the cell that contains text needs the following steps:  

Select, copy and paste > the Data tab > Text to Columns > Text to Column > Fixed width > Next > Create a break line(separate the first two letter) at Data preview > Next > General at Column data format > Finish.

· Delimited Text:

Delimited means characters such as commas or tabs separate each field.

In this case, we want to separate the first and last name into two columns.

Note: in previous tutorials, we have learn how to connect a list of separate first and last names with the CONCATENATE Function

1. Open your table in WPS Spreadsheet.

2. Select the array A2:A26, copy and paste it to B2:B26, because this is the place we want the divided text to return.

3. Select B2:B26, head to the Data tab, and click Text to Columns > Smart Split Columns > Finish.

A traditional way (the same in Microsoft Excel) to split the cell that contains text needs the following steps:  

Select, copy and paste > The Data tab > Text to Columns > Text to Column > Delimited > Next > Check Space at Delimiters > Next > General at Columndata format > Finish.