Microsoft Excel logo on a green background

If text is a big part of the data in your spreadsheet, you may need to adjust it to fit properly. The TRUNC function in Microsoft Excel works only with numbers. So if you want to truncate text, here’s how.

There are many useful functions for working with text in Excel. Three of those functions help you truncate text in a cell. These are RIGHT, LEFT, and MID, and each has a variation for using bytes instead of characters.

Truncate Text in Excel with RIGHT or RIGHTB

The RIGHT function uses the number of characters for a single-byte character set (SBCS) while RIGHTB uses a number of bytes for a double-byte character set (DBCS). Both functions work the same way with only that difference. So, you can use whichever works best for you.

RELATED: How to Count Characters in Microsoft Excel

The syntaxes are RIGHT(text, number_characters) and RIGHTB(text, number_bytes) with the first argument for each formula required. You can enter the cell reference and keep what’s on the right.

To keep 12 characters on the right from the text string in cell A2, you would use this formula:

=RIGHT(A2,12)

Keep 12 characters on the right

Advertisement

As another example, we want to keep only the last word and punctuation. This formula retains the last eight characters on the right.

=RIGHT(A2,8)

Keep eight characters on the right

Truncate Text in Excel with LEFT or LEFTB

The LEFT and LEFTB functions work like the RIGHT and RIGHTB functions where you use either the number of characters or bytes respectively. But with these functions, you’re shortening your text string from the opposite side.

RELATED: 12 Basic Excel Functions Everybody Should Know

The syntaxes are LEFT(text, number_characters) and LEFTB(text, number_bytes) with the first argument for each formula required. Again, insert the cell reference for the text and keep what’s on the left side.

To keep 32 characters on the left from the text string in cell A2, you would use this formula:

=LEFT(A2,32)

Keep 32 characters on the left

For another example, we want to keep only the first word in the string. This formula retains only the first four characters on the left.

=LEFT(A2,4)

Keep four characters on the left

Truncate Text in Excel with MID or MIDB

If the text that you want to keep is in the middle of a text string, you’ll use the MID or MIDB functions. These functions are like the other two in that you enter a number or characters for MID and number of bytes for MIDB.

RELATED: How to Shrink or Expand Cells to Fit Text in Microsoft Excel

The syntaxes are MID(text, start, number_characters) and MIDB(text, start, number_bytes) with all arguments for each formula required. You can use a cell reference for the first argument, the number of the starting character or byte for the second, and the number to keep for the third.

Advertisement

Here, we’ll remove everything from the string except for a middle portion. With this formula, the text is in cell A2, we want to start with the 35th character, and keep only 24 characters.

=MID(A2,35,24)

Keep 24 characters in the middle

As another example, using the following formula you can shorten the text in cell A2 and keep only the second word. We use 6 for the start argument and 3 for the number_characters argument.

=MID(A2,6,3)

Keep three characters in the middle

Shortening text in your sheet may be necessary or simply something you prefer. These functions and formulas help you truncate text in Excel from the right, left, or middle, as you need it.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »