How to Truncate Text 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.
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).
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.
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.
Leave a Reply
Leave a Reply
