How to Truncate Text in Excel

PDF download Download Article
PDF download Download Article

This wikiHow teaches you how to shorten the appearance of data in Microsoft Excel. To do this, your full, unshortened data will need to be entered into Excel first.

Truncating Text in Excel

  1. Select the cell where you want to insert the truncated text.
  2. Enter the formula =DIRECTION(Cell Name, Number of characters to display) .
  3. Replace “DIRECTION” with “LEFT” if you want the characters to be displayed from the left.
  4. Replace “DIRECTION” with “RIGHT” if you want the characters to be displayed from the right.
  5. Replace “Cell Name” with the letter and number of the cell the text is in (e.g., A3, C5).
  6. Specify the number of characters from your original text that you want to be displayed.
  7. Press Enter when your formula is complete.
Method 1
Method 1 of 3:

Truncating Text Using the LEFT and RIGHT Formulas

PDF download Download Article
  1. If you have an existing document with your data already entered, you can double-click it to open it; otherwise, you'll need to open a new workbook and enter your data now.
  2. This method is useful for text that you already have in your spreadsheet.
    • Note that this cell must be different than the cell in which your target text appears.
    Advertisement
  3. The LEFT and RIGHT formula are built on the same premise, though the LEFT formula displays characters from the left side of your cell's text and the RIGHT formula displays characters from the right. The formula is "=DIRECTION(Cell Name, Number of characters to display)" without the quotation marks. For example: [1]
    • =LEFT(A3, 6) displays the first six characters in cell A3. If the text in A3 says "Cats are better", the truncated text will read "Cats a" in your selected cell.
    • =RIGHT(B2, 5) displays the last 5 characters in cell B2. If the text in B2 says "I love wikiHow", the truncated text will read "kiHow" in your selected cell.
    • Keep in mind that spaces count as characters.
  4. Your selected cell will automatically fill with the truncated text.
    5. Advertisement
Method 2
Method 2 of 3:

Truncating Text Using the MID Formula

PDF download Download Article
  1. This cell must be different than the cell in which your target text appears.
    • If you haven't already added your data to Excel, you'll need to do so first.
  2. MID chops characters off of the beginning and end of your selected cell's text. To set up the MID formula, you type "=MID(Cell Name, Starting Character Number, Number of characters to display)" without the quotation marks. For example:
    • =MID(A1, 3, 3) displays three characters from cell A1, the first of which is the third character from the left in the text. If A1's text says "racecar", the truncated text will read "cec" in your selected cell.
    • Similarly, =MID(B3, 4, 8) displays eight characters from cell B3, starting with the fourth character from the left. If B3's text says "bananas aren't people", the truncated text will read "anas are" in your selected cell.
  3. This will add the truncated text to your selected cell.
    4. Advertisement
Method 3
Method 3 of 3:

Splitting Text Into Multiple Columns

PDF download Download Article
  1. This should be a cell that has more characters than space.
  2. It's in the toolbar at the top of your Excel page.
  3. You'll find this option in the "Data Tools" section of the Data tab.
    • This function divides the cell’s contents of one Excel cell into separate columns.
  4. After clicking on Text to Columns window will pop up, called "Convert Text to Columns Wizard Step 1 of 3." The window will have two selections: "Delimited" and "Fixed Width." Delimited means that characters, such as tabs or commas will divide each field. You will usually select delimited when you’re importing data from another application, such as a database. The fixed width option means that the fields are lined up in columns that have spaces between the individual field.
  5. This window shows three options. If you want to create a break line, click on the position where you want the text to break. If you want to delete the break line, double click on the line. To adjust the line, click and drag it around the data.
  6. This window has several options, "General", "Text," "Date" and "Do not import column (skip)." Unless you want to force your cell's formatting to do something different than its natural state, you can skip this page.
  7. Your text should now be divided between two or more cells.
    8. Advertisement

Community Q&A

Search
Add New Question
  • Question
    How do I remove all the text after a period?
    Community Answer
    It depends on whether it is the first period possible, or a later one. I will address it being the first period as the alternatives are a little involved. Use the LEFT() function. The thing you need to work hard for is where is the period? For that, you can use the FIND() function which will locate its position. Since you want precisely that many characters fro the left of the text, nothing more is needed. = LEFT( A1, FIND( ".", A1 ) ). You can use FIND() this way for many things.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 3 Helpful 11
  • Question
    How can I change all instances of a word to a letter in Excel?
    Community Answer
    Use the SUBSTITUTE() function, with the word you wish to change into a letter being the text to go away ("old_text") and the letter you desire in their places being the text to put in place ("new_text").
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 0 Helpful 2
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Create Pivot Tables in Microsoft Excel to Analyze Data
      How to
      Apply Conditional Formatting in Excel
      3 Simple Ways to Edit Data in MS Excel
      How to
      Sort Microsoft Excel Columns Alphabetically
      How to
      Group and Outline Excel Data: Easily Collapse Rows of Info
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      Advertisement

      References

      1. ↑ https://www.techonthenet.com/excel/formulas/left.php

      About This Article

      Article Summary X

      1. Select the cell where you want the truncated text to appear.
      2. Type the LEFT or RIGHT formula in the cell.
      3. Press Enter .

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 556,619 times.

      Reader Success Stories

      • Renata Wick

        Aug 24, 2016

        "It works great. Very easy."
      Share your story

      Is this article up to date?

      Advertisement