Excel: How to Split String by Specific Length

You can use the Text to Columns feature within the Data tab in Excel to split a string by a specific length into multiple columns.

The following example shows how to do so in practice.

Example: How to Split String by Specific Length n Excel

Suppose we have the following column of employee ID’s in Excel:

Each employee ID consists of nine total characters.

Suppose we would like to split each employee ID string by chunks of three letters into three separate columns.

To do so, we can highlight the cell range A2:A9, then click the Data tab along the top ribbon, then click the Text to Columns icon within the Data Tools group:

In the new box that appears, check the box next to Fixed Width, then click Next:

In the white box, click the locations where you would like to split the text.

We will click once after the first set of three characters, then click again after the second set of three characters to create two visible lines:

Then click Next.

Then type $C$2 in the Destination box, then click Finish:

Each string in column A will automatically be split into three new columns that each contain three characters from the Employee ID string:

Excel split string by length

Note: In this example we chose to split a string based on a length of 3, but you can choose to split by any length you’d like by specifying the line breaks in the second step of the Text to Columns feature.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: How to Split String by Comma
Excel: How to Split Addresses into Multiple Cells
Excel: How to Split Word into Individual Letters

Leave a Reply

Your email address will not be published. Required fields are marked *