How-To

How to Truncate Text in Excel

How to Remove Time From a Date in Excel

Truncating a text string in Excel allows you to cut off unnecessary parts to it. If you’re unsure how, follow this guide.

The beauty of Microsoft Excel is in the functionality it offers. If you want to do something in Excel, you only need to find the right function to do it.

If you’re trying to clean up some text, for example, you might want to consider truncating it. Thankfully, Excel offers a number of functions to help you cut down a text string in Excel.

If you’re unsure how to truncate text in Excel, follow our steps below.

How to Truncate Text in Excel Using RIGHT, LEFT, or MID Functions

The best way to truncate text in excel is to use the RIGHT, LEFT, or MID functions. These functions all work in very similar ways to help you remove a number of characters from a text string in Excel.

Explaining RIGHT, LEFT, and MID Functions in Excel

As the names suggest, these functions allow you to remove a number of characters from a text string, starting from the left or right. You can also start it from a custom position using MID.

The syntax for these functions is as follows:

=RIGHT(text, num_chars)

=LEFT(text, num_chars)

=MID(text, start_num, num_chars)

LEFT and RIGHT work in the same way. Replace text with the text string (or cell reference containing a text string) and replace num_chars with the number of characters to keep from the starting point on the left or right. For MID, replace text and num_chars in the same way.

You’ll also need to replace start_num with the character position you want to start in for MID. For example, if a text string is 8 characters long, and you select a start_num value of 3, the formula will ignore characters 1 and 2 and show the remaining characters (based on the num_chars value) moving left.

Example MID RIGHT and LEFT formulas in Excel

The LEFT, MID, and RIGHT formulas in Excel.

As we’ve mentioned, you can replace the text argument with either a text string or a cell reference containing a string. If you want to use a text string the formula directly, however, make sure to wrap the text string in quote marks to ensure it’s parsed correctly.

How to Use LEFT, RIGHT, and MID in Excel

To help explain how to use LEFT, RIGHT, and MID functions to truncate text in Excel, we’ll use a scenario.

A cell (A2) contains a text string—It is a nice day today in May. This text string contains eight words and 29 characters. If you want to remove any of these characters, you can use the RIGHT, LEFT, or MID functions to do it. Let’s assume you want to show ten characters from the text string mentioned above.

For MID, we’ll assume you want to start after the letter a (beginning with character number 8).

An example of the MID RIGHT and LEFT Functions in Excel

Using each of these functions would return different results:

  • LEFT: It is a ni
  • MID:  nice day (starting with a space)
  • RIGHT: day in May

You can apply the rules from this scenario to your own LEFT, RIGHT, or MID formulas.

How to Truncate Number Values in Excel

The steps above only work for truncating text strings. If you want to truncate a number, you’ll need to use a function like TRUNC instead.

The TRUNC Function in Excel to one decimal place

An example of TRUNC in Excel.

TRUNC is designed for integer values—whole numbers that lack decimal places. You can use TRUNC to remove the decimal places and return whole numbers instead.

If you want to know how to use this function, you can check out our guide to using TRUNC in Excel.

Cleaning Up Data in Microsoft Excel

An Excel spreadsheet doesn’t have to be perfect—but it does have to be right. Thanks to the steps above, you should now know how to truncate text in Excel, allowing you to clean up your spreadsheet data.

If you’re working with large data sets, why not split them into separate sheets? You can then pull the data between sheets in Excel to make it easier to handle.

Are you working with colleagues who have accessibility issues? Don’t forget to use the Office accessibility checker to see if your document needs any changes.

Click to comment

Leave a Reply

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

 

To Top