Guide: Mastering the Art of Text Trimming in MS Excel
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)
- Title: Guide: Mastering the Art of Text Trimming in MS Excel
- Author: Joseph
- Created at : 2024-08-31 22:04:27
- Updated at : 2024-09-01 22:04:27
- Link: https://windows11.techidaily.com/guide-mastering-the-art-of-text-trimming-in-ms-excel/
- License: This work is licensed under CC BY-NC-SA 4.0.