How to Separate Phrases by Length in Excel

Microsoft Excel offers tools to subdivide text.
... Jupiterimages, Brand X Pictures/Brand X Pictures/Getty Images

The "Text to Columns" feature in Microsoft Excel 2013 can separate longer text passages into smaller, equal-length phrases that overwrite data in neighboring cells. The total number of columns used depends on the original text length. For example, if you wanted to separate a 100-character text block into 15-character phrases, you would need seven columns, including the original column. That is because 15 evenly goes into 100 six times with a 10-character remainder that goes into the seventh column.

Enter the text you wish to separate in column "A." Make sure no other data resides to the right of this data or it could be deleted.

Click the first entry, hold the "Shift" key, and then click the last entry to highlight all data.

Click the "Data" tab, and then select "Text to Columns" in the Data Tools group.

Click "Fixed Width" and select "Next." By default, the text is separated wherever spaces appear in the text, but you can adjust the divider lines in the preview.

Double-click any existing divider line to remove it, or drag it to another location. Click at equally spaced intervals to create divider lines. Scroll to the right to continue adding divider lines to the right-most side of the preview. In the previous example of 15-character phrases, click the "15," "30," "45," "60," "75" and "90" marks to add divider lines that take into account the longest 100-character entry. Make sure no other divider lines appear at other marks.

Click "Next," select "Text" and then click "Finish" to separate the text into equal-length sections. If data exists in neighboring cells, a warning appears; click "OK" to overwrite this data. In the example, the text is separated into 15-character phrases in columns A through G.

  • You can also separate text using the Left, Right and Mid functions by extracting phrases from the left, right or middle of text. As an example, the formula "=Left(A1,15)" extracts the first 15 characters from cell A1, and "=Right(A1,15)" extracts the last 15 characters. The formula "=Mid(A1,16,15)" starts with the 16th character and extracts the next 15 characters.
  • The Len function helps identify text length. As an example, the function "=Len(A1)" would return "100" if cell A1 contained 100 characters.
  • Combined with the If and And functions, you can copy only text of a certain length. So, if you wanted to copy text from cell A1 only if it was longer than 15 characters, use the following function:
  • =If(Len(A1)>15,A1,"")
  • If you wanted to look for text greater than 15 characters, but less than or equal to 30 characters, use the following function:
  • =If(And(Len(A1)>15,Len(A1)<=30),A1,"")
×