Guide: Mastering the Art of Text Trimming in MS Excel

Quick Links

• Truncate Text in Excel with RIGHT or RIGHTB
• Truncate Text in Excel with LEFT or LEFTB
• Truncate Text in Excel with MID or MIDB

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)

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)

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)

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)

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.

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)

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)

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.

Related: How to Add Text to a Cell With a Formula in Excel

Also read: