Excel Formula to Remove Characters from Left and Right

Sharing is caring!

Do you need to remove characters from the left and right sides of text in Microsoft Excel? There are a few different formulas you can use to accomplish this efficiently. The main functions to use are the LEFT, RIGHT, and LEN functions combined with basic math. This allows you to extract just the portion of text you want to keep by removing a specified number of characters from the start and end.

In this article, we’ll walk through exactly how to set up the formulas step-by-step with example data. By the end, you’ll be able to easily trim unwanted characters from your text strings in Excel whenever needed.

What the LEFT, RIGHT, and LEN Functions Do

To understand how to remove characters from the sides of text, it helps to first know what these key functions do:

LEFT Function

• The LEFT function extracts a set number of characters from the left side (start) of a text string.
• It takes two arguments:
  • The cell with the text
  • The number of characters to extract
• For example, =LEFT(A1, 5) will return the leftmost 5 characters from cell A1.

RIGHT Function

• The RIGHT function is the opposite of LEFT. It extracts a set number of characters from the right side (end) of a text string.
• Like LEFT, it also takes two arguments:
  • The cell reference
  • Number of end characters to retrieve
• For example, =RIGHT(A1, 6) will return the rightmost 6 characters from the text in cell A1.

LEN Function

• The LEN function counts the total number of characters in a text string, including spaces and punctuation.
• It just takes one argument – a reference to the cell containing the text.
• For example, =LEN(A1) will return a count of the total characters in cell A1.

Remove Characters from the Left Side Only

Let’s say you have text strings like this in column A and you want to remove the first 4 characters from each one:

Here are the steps to remove characters from just the left side:

1. In an empty column, enter the formula: =RIGHT(A1,LEN(A1)-4)
2. This tells Excel to extract all characters except the leftmost 4 from cell A1.
3. Copy the formula down the column to trim the other text strings too.

The results will look like this:

The LEN function counts the total characters, then we subtract 4 so the RIGHT function extracts everything except the first 4 characters.

Remove Characters from the Right Side Only

To remove characters from just the right side instead, we use a similar formula but with the LEFT function:

1. =LEFT(A1,LEN(A1)-4)
2. Copy down the column.

Using the same example as before, this is the result:

Now the rightmost 4 characters are stripped off instead. The LEFT function extracts the total length minus 4 characters, starting from the left side.

Remove Characters from Both Left and Right Sides

To remove an equal number of characters from both sides simultaneously:

1. =MID(A1,3,LEN(A1)-5)
2. Adjust the “3” and “5” to change the trim amount. The “5” should be 2X the “3” amount.
3. Copy the formula down.

In this example, 2 characters are removed from the left and 3 from the right:

The MID function extracts a substring from the middle of the text. It takes 3 arguments:

1. The cell reference
2. The starting position (removes characters before this)
3. Number of characters to extract (total length minus left and right trim amounts)

Removing Differing Amounts from Left and Right

What if you need to remove a different number of characters from each side, like 3 from the left and 5 from the right? Just adjust the MID formula:

1. =MID(A1,4,LEN(A1)-8)
2. The “8” value should equal the “4” amount plus the number of end characters to remove.
3. Copy down as usual.

This strips off the first 3 and last 5 characters:

Trim Based on Specific Characters

Sometimes you may need to remove all characters before or after a specific character rather than a set number. For this, use the SEARCH and SUBSTITUTE functions. Here are some examples:

Trim Text Before a Character

To trim everything before the first dash in the example text strings:

1. =RIGHT(A1,LEN(A1)-SEARCH(“-“,A1))
2. Or more universally: =RIGHT(A1,LEN(A1)-SEARCH(“-“,A1,1))

This removes “abcd”, “lmno”, and “efgh” respectively:

The SEARCH function finds the position of the first dash. That number is subtracted from the total length to remove the characters before it.

Trim Text After a Character

To instead remove everything after the second dash:

1. =LEFT(A1,SEARCH(“-“,A1,SEARCH(“-“,A1)+1)-1)
2. Adjust the +1 inside the SEARCH to trim after the 1st or 3rd occurrence of the character.

Using our example, this returns:

Here the SEARCH within a SEARCH pinpoints the location of the 2nd dash. The LEFT function extracts the text up to that character position.

Trim Text Between Two Characters

Expanding further, you can extract just the text between two specific characters:

1. =MID(A1,SEARCH(“-“,A1)+1,SEARCH(“-“,A1,SEARCH(“-“,A1)+1)-SEARCH(“-“,A1)-1)

Applied to the sample data:

This complex formula uses SEARCH to find the positions of the 1st and 2nd dashes, then does some math to extract only the text in between them.

Remove Unwanted Characters from Left and Right

Finally, to remove specific unwanted characters from the left and right sides, use SUBSTITUTE like this:

1. =SUBSTITUTE(SUBSTITUTE(A1,”abc”,””),”-wxyz”,””)
2. Replace “abc” and “-wxyz” with the actual text to remove.
3. Add more nested SUBSTITUTE functions to remove additional strings.

In this case:

The SUBSTITUTE function replaces matching text with an empty string, essentially deleting it. By nesting two SUBSTITUTE functions, we can remove text from both the start and end.

Summary

To recap, here are the main formulas to remove characters from the left and right sides of text in Excel:

I hope these formulas help you manipulate text in Excel more easily. With some practice, you’ll be able to remove characters from the left, right, or both sides of text to get your data looking just how you need it. Being able to clean up text this way is a big time-saver, especially when working with large datasets exported from databases or other systems that add unwanted characters. Try out the different formulas and see which ones work best for your use case.

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.