- ➥ Go to the beginning of the free online Excel Course
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 to truncate the 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 the Microsoft 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 RIGHTTB
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 for each formula. It is possible to enter a cell reference and match what is on the right.
For example, to keep 12 characters to the right of the text string in cell A2, we'll have to use the following formula: =RIGHT(A2,12)
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 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 keep text in the middle of a text string, we can use the MID or MIDB functions. They work in the same way as the previous examples, we enter a number or characters for MID and a number of bytes for MIDB..
In this example, we are going to remove everything from the string except 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)
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.