How to Truncate Text in Excel Based on Character?

Sharing is caring!

Truncating text in Excel is useful when you want to shorten strings to a specific number of characters. Whether you’re cleaning data, formatting reports, or preparing files for import/export, knowing how to truncate text based on character length can save time and prevent errors.

In this guide, you’ll learn different methods to cut text by character length using Excel formulas and tools. We’ll explore built-in functions like LEFT, RIGHT, MID, LEN, and how to combine them effectively. These techniques work in all recent versions of Microsoft Excel.

Methods to Truncate Text Based on Character

There are several ways to truncate text in Excel. Let’s look at the most common and useful methods.

1) Using the LEFT Function

The LEFT function returns a specific number of characters from the start of a text string.

Syntax:

=LEFT(text, num_chars)

• text: The string you want to cut.
• num_chars: The number of characters you want to keep.

Example:

=LEFT(A2, 10)

This keeps the first 10 characters of the text in cell A2.

2) Using the RIGHT Function

If you need to keep characters from the end of a string, use the RIGHT function.

Syntax:

=RIGHT(text, num_chars)

Example:

=RIGHT(A2, 5)

This returns the last 5 characters of the string in cell A2.

3) Using the MID Function

Use the MID function if you need to extract a substring from the middle of a string.

Syntax:

=MID(text, start_num, num_chars)

• start_num: Position to begin extracting.
• num_chars: How many characters to keep.

Example:

=MID(A2, 3, 5)

This returns 5 characters starting from the 3rd position.

4) Truncate Text Without Cutting Words

To truncate a string without breaking a word, you’ll need to locate the last space character before the cutoff and cut the text accordingly.

Use this formula to truncate up to 20 characters without breaking a word:

=LEFT(A2,FIND("@",SUBSTITUTE(LEFT(A2,20)," ","@",LEN(LEFT(A2,20))-LEN(SUBSTITUTE(LEFT(A2,20)," ","")))))

Explanation:

• This formula finds the last space within the 20-character limit and cuts the text at that point.
• This is useful for preview text or summaries.

5) Using Text Truncation with IF Conditions

Sometimes you only want to truncate if the text exceeds a certain length. Combine LEN and IF like this:

=IF(LEN(A2)>15, LEFT(A2,15), A2)

• If the text in cell A2 has more than 15 characters, it’s truncated.
• If not, the full text remains.

6) Use Flash Fill for Quick Manual Truncation

Flash Fill in Excel can auto-complete patterns. If you type the shortened version in one cell, Excel can guess the rest.

Steps:

1. In a new column, manually type a truncated version (e.g., only first 5 characters).
2. Press Enter.
3. Click the next cell and press Ctrl + E or go to Data > Flash Fill.

Flash Fill is useful for small datasets where formulas aren’t needed.

7) Truncate Text with Power Query

For larger datasets or automation, use Power Query to cut text strings.

Steps:

1. Select your data and go to Data > Get & Transform > From Table/Range.
2. In Power Query Editor, select the column.
3. Go to Add Column > Extract > First Characters.
4. Enter how many characters to keep.
5. Click Close & Load.

Power Query doesn’t require formulas and is ideal for advanced tasks.

Dynamic Truncation Using Cell References

You can make your formulas more flexible by referencing the character limit from another cell.

Example:

=LEFT(A2, B1)

If B1 contains 12, this formula keeps the first 12 characters of A2. This makes your sheet easier to maintain and adjust.

Combine Truncation with Other Excel Functions

You can use truncation inside other functions such as CONCAT, TEXTJOIN, or IFERROR to build complex formulas.

Example (Combine LEFT with CONCAT):

=CONCAT(LEFT(A2,10), "...")

This adds ellipses after the truncated text to show that content has been cut.

Example (Truncate Email Usernames):

=LEFT(A2, FIND("@", A2)-1)

Extracts the username before the @ symbol in email addresses.

Truncating Text in Multiple Cells at Once

To apply truncation to a range:

1. Write the formula in the first cell (e.g., =LEFT(A2, 8)).
2. Drag the fill handle down to apply it to other cells.

Alternatively, use array formulas in newer Excel versions:

=LEFT(A2:A10, 8)

This automatically fills the column with truncated text (in Excel 365 or 2021).

Limitations of Text Truncation in Excel

• Formulas recalculate when source text or character limits change.
• Truncated text loses information unless the full version is stored elsewhere.
• Multi-byte characters (like Chinese or emojis) can affect character count.
• Flash Fill and Power Query are manual steps unless automated.

Final Thoughts

Truncating text in Excel is simple with functions like LEFT, RIGHT, and MID. These formulas give you control over how much text to keep and where to cut. You can combine them with logic functions like IF and LEN, or use tools like Flash Fill and Power Query for more automation.

Knowing when and how to truncate based on characters helps you format data professionally, prepare files for upload, or clean entries quickly. With these tools in hand, your Excel workflow becomes smoother and more efficient.

FAQS

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.