+3 votes
56 views
How to truncate text in Microsoft Excel

in Guides by (552k points)
reopened | 56 views

1 Answer

+4 votes
Best answer

Truncate text in Microsoft Excel with RIGHT or RIGHTTB
With LEFT or LEFTB
Truncate text in Excel with MID or MIDB

  • ➥ 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.

image

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)

image

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..

image

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)

image

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.



by (3.5m points)
edited

Related questions

+3 votes
1 answer
asked Dec 22, 2021 in Guides by backtothefuture (552k points) | 126 views
+5 votes
1 answer
asked Oct 30, 2022 in Guides by backtothefuture (552k points) | 66 views
+3 votes
1 answer
asked Jan 15, 2022 in Guides by backtothefuture (552k points) | 125 views
+5 votes
1 answer
asked Jan 10, 2022 in Guides by backtothefuture (552k points) | 105 views
+4 votes
1 answer
asked Nov 21, 2021 in Guides by backtothefuture (552k points) | 110 views
Sponsored articles cost $40 per post. You can contact us via Feedback

Most popular questions within the last 30 days

10,659 questions
10,791 answers
510 comments
3 users