How to truncate text in Microsoft Excel?

If the text represents a large portion of your spreadsheet data, you may need to adjust it to fit properly. Microsoft Excel’s TRUNC function only works with numbers. So if you want to truncate some text, here’s how to do it.

There are many useful functions for working with text in Excel. Three of these functions help you truncate text in a cell. They are the RIGHT, LEFT, and AVERAGE functions, and each of them has a variation for using bytes instead of characters.

CONVERT APPLE NUMBERS IN MICROSOFT EXCEL

Truncate text in Excel with RIGHT or RIGHTB

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

The syntaxes are RIGHT(text, num_characters) and RIGHTB(text, num_bytes), with the first argument required for each formula. You can enter the cell reference and keep whatever is on the right.

To keep 12 characters to the right of the text string in cell A2, you need to use this formula:

=RIGHT(A2,12)

Keep 12 characters to the right

Another example, we want to keep only the last word and the punctuation. This formula keeps the last eight characters on the right.

=RIGHT(A2,8)

Keep eight characters to the right

Truncate text in Excel with LEFT or LEFTB

The LEFT and LEFTB functions work like the RIGHT and RIGHTB functions: you use the number of characters or bytes, respectively. But with these functions you will shorten your text string on the opposite side.

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

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

=LEFT(A2,32)

Keep 32 characters to the left

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

=LEFT(A2,4)

Keep four characters to the left

Truncate text in Excel with MID or MIDB

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

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

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

=AVERAGE(A2,35,24)

Keep 24 characters in the middle

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

=AVERAGE(A2,6,3)

Keep three characters in the middle

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

Leave a Comment