How to Truncate Text in Microsoft Excel

Admin

It is normal that at some point we have too much data in our calculation daughter and we have to make some adjustments. For this it is useful truncate text in Microsoft Excel. The problem is that the TRUNC function works only with numbers; but can this be done with text? Yes, it is totally possible.

Excel allows us to perform various actions with the text in our spreadsheets, such as rotating it, formatting it, counting cells with text, etc. Additionally, if you are new to Microsoft’s office suite, we recommend that you learn some basic Excel functions that will make your job easier.

Truncate text in Microsoft Excel with RIGHT or RIGHTB

The “Right” or “Derecha” function uses multiple characters for a single-byte character set (SBCS), while “Rightb” uses a number of bytes for a double-byte character set (DBCS). The two functions work in the same way, although this is the only difference. Therefore, it is possible to use the one that suits us best.

The syntaxes are RIGHT(text, numer_characters) and RIGHTB(text, numer_bytes) with the first argument to each formula. It is possible to enter a cell reference and arrange what is on the right.

For example, to keep 12 characters to the right of the text string in cell A2, we would have to use the following formula: =RIGHT(A2,12)

The last word of the text would remain in Excel.

Let’s see another practical example, if we want to keep only the last word and punctuation. This form will keep the last eight characters to the right: =RIGHT(A2,8)

With LEFT or LEFTB

Not much explanation is needed in this case, it’s actually the same as the previous example, just shortening the text string on the opposite side. The way to implement it is exactly the same as the examples provided above. Only that we must replace “RIGHT” with “LEFT”

Truncate text in Excel with MID or MIDB

In case we need to preserve text in the middle of a text string, we can use the MID or MIDB functions. They work the same way as the previous examples, we enter a number or characters for MID and a number of bytes for MIDB.

We save the middle text.

In this example, we are going to remove everything from the string except for the middle part. Thanks to this formula, the text is in cell A2 and we want to start with character 35, although we are going to keep 24 characters: =MID(A2,35,24)

So we can truncate the text in Excel.

We can see another example in the following formula, it is possible to shorten the text in cell A2 and keep only the second word. We will use 6 for the start of the argument and 3 for the number of characters: =MID(A2,6,3)

On many occasions it may be necessary to shorten the text in our document. Thanks to these simple functions and formulas we can truncate the text in Microsoft Excel to the right, middle or left.

Next Post

Sprinter opens three new stores employing 55 people

Image of the exterior of the new Sprinter store in Talavera de la Reina. (27-5-2022). Sprinter has opened the doors of three new stores in Talavera de la Reina, Figueras, and an international opening in Amsterdam. Throughout 2022 a total of 12 more openings are planned. Sprinter, the sports retailer, […]
Sprinter opens three new stores employing 55 people