How to truncate text in Excel

You can watch a video tutorial here.

Although Excel is well suited for calculation, it has several functions to manipulate and format text. These functions are useful especially when cleaning data. There may be a case where you want to truncate the text in a cell as all the text is not required. 

The following functions can be used to truncate text:

  1. LEFT() function: this returns a specified number of characters from the left of a string
    1. Syntax: LEFT(text, number of characters)
      1. text: the string from which the characters are to be extracted
      2. number of characters: the number of characters to extract
  2. MID() function: this returns a specified number of characters from a string
    1. Syntax: MID(text, start number, number of characters)
      1. text: the string from which the characters are to be extracted
      2. start number: the number of the character from which the extraction is to start
      3. number of characters: the number of characters to extract

Option 1 – Use the LEFT() function

Step 1 – Create the formula

  • Select the cell in which the truncated text is to appear
  • Type the formula using cell references:

=LEFT(Article, 100)

  • Press Enter
  • The first 100 characters are retained and the rest of the text is truncated

Option 2 – Use the MID() function

Step 1 – Create the formula

  • Select the cell in which the truncated text is to appear
  • Type the formula using cell references:

=MID(Article, 1, 100)

  • Press Enter
  • The first 100 characters are retained and the rest of the text is truncated