How to Truncate Text in Excel to a Set Length

To truncate text in Excel, use LEFT to keep a set number of characters from the start: =LEFT(A2,20). Add IF and LEN when you want an ellipsis on long text only, or a few more functions to cut at a whole word. This guide is for anyone fitting product names, titles or comments into a fixed-width label, export or report column.

How to truncate text with LEFT#

LEFT keeps the first characters of a text and drops the rest. Its syntax has two parts:

=LEFT(text, num_chars)

All the examples use this list of product names in A2:A5:

=LEFT(A2,20) returns Wireless ergonomic k. Spaces and punctuation count as characters. Text shorter than the limit comes back unchanged, so =LEFT(A4,20) returns Desk lamp. LEFT always returns text, even from a number: =LEFT(12345,3) gives the text 123, so wrap it in VALUE if you need to calculate with the result. The LEFT function guide lists every detail, and LEFT, RIGHT and MID explained covers the sister functions.

How to check the length first with LEN#

LEN counts the characters in a cell, including spaces. Use it to see which rows are too long before you cut anything:

=LEN(A2)

This returns 42 for the keyboard. To highlight every name over 20 characters, select the names and add a conditional formatting rule with the formula =LEN(A2)>20. The LEN function guide and the article on counting characters and words go further.

How to truncate text and add an ellipsis#

An ellipsis is the "..." that shows text was cut. Add it only when the text is too long, and keep the total length within the limit:

=IF(LEN(A2)>20,LEFT(A2,17)&"...",A2)

The & sign joins two pieces of text; see joining text with & and CONCAT. Results on the sample:

The last one has a space before the dots, because character 17 is a space. Wrap LEFT in TRIM to remove it: =IF(LEN(A2)>20,TRIM(LEFT(A2,17))&"...",A2) gives Noise-cancelling....

How to truncate text at a whole word#

Cutting in the middle of a word looks untidy. To cut at the last full word, take one character more than the limit and then go back to the last space. In Microsoft 365 or Excel 2024, TEXTBEFORE makes this short:

=IF(LEN(A2)<=20,A2,TEXTBEFORE(LEFT(A2,21)," ",-1))

LEFT(A2,21) takes 21 characters, and TEXTBEFORE(..., " ", -1) returns everything before the last space. Taking 21 instead of 20 means a word that ends exactly at character 20 is kept. Results:

In older versions, swap the last space for a marker character and find it:

=IF(LEN(A2)<=20,A2,LEFT(A2,FIND("~",SUBSTITUTE(LEFT(A2,21)," ","~",LEN(LEFT(A2,21))-LEN(SUBSTITUTE(LEFT(A2,21)," ",""))))-1))

The LEN minus LEN part counts the spaces, so SUBSTITUTE replaces only the last one with ~, and FIND gives its position. Both versions fail when the first 21 characters contain no space at all, such as a long code. Wrap the formula in IFERROR with LEFT(A2,20) as the fallback. The guide to extracting the last word uses the same space-counting idea.

How to remove the last few characters#

Sometimes you know how many characters to drop rather than how many to keep. Subtract them from the length:

=LEFT(A2,LEN(A2)-4)

With INV-2041-OLD in A2, this removes the last four characters and returns INV-2041. To drop characters from the start instead, use RIGHT with the same idea; see how to remove the first characters from text. To take a piece from the middle, use the MID function.

Truncating text vs hiding it#

A formula cuts the text for real; formatting only hides it. If text just spills into the next column on screen, you may not need a formula at all. Excel stops the overflow when the cell to the right is not empty, and turning on Wrap Text keeps the text inside its own column. The full text is still in the cell.

Use a formula when the shorter text itself matters, such as an export with a character limit or a label template. To stop long entries in the first place, use Data > Data Validation, set Allow to Text length, choose less than or equal to and enter the maximum.

Note that TRUNC is not a text function. It cuts decimals off numbers: =TRUNC(3.789,1) returns 3.7. On text, it returns #VALUE!.

How to replace the original text with the truncated version#

Formulas need the original column to keep working. To keep only the short version:

1. Write the formula in a helper column and fill it down (Windows: Ctrl+D; Mac: ⌘D).
2. Copy the results with Ctrl+C (Mac: ⌘C).
3. Select the original column and use Home > Paste > Values.
4. Delete the helper column.

Keep a copy of the full text somewhere first if there is any chance you'll need it again.

Practise it#

Truncating text is LEFT and LEN working together, with & for the dots. Practise LEFT, RIGHT, MID and LEN in a live spreadsheet: you type each formula and it checks it instantly. Then practise joining text with &, CONCAT and TEXTJOIN to build labels from the pieces you cut.