Table of Contents
If you need to split a string in Excel by a specific length, you can use the “LEFT” and “RIGHT” functions. The LEFT function allows you to extract a specific number of characters from the beginning of a string, while the RIGHT function extracts a specific number of characters from the end of a string. You can use these functions in combination with the “LEN” function, which counts the number of characters in a string, to specify the desired length for the split. This method allows you to split a string into smaller segments based on a specific length, providing greater control and flexibility in organizing your data in Excel.
Excel: 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:
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: