How to Trim Part of Text in Excel (9 Easy Methods)

While working in Microsoft Excel, you may need to trim part of a text string. For example, you might require removing a certain part of a text string. Besides that, sometimes you have to delete part of texts before/after a specific character. In this article, I will discuss several easy and quick methods to trim part of texts in excel.

1. Using Find and Replace Tool to Trim Part of Text in Excel

First of all, I will use the Find and Replace option in excel to cut a portion of text in excel. Suppose, I have the dataset (B5:B10), containing the below data. Now I will replace the text ‘Full Name:’ with a blank.

Steps:

• First, select the dataset and press Ctrl + H to get the Find and Replace dialog.
• When the Find and Replace dialog appears, type the part of the text that you want to trim in the Find what field. Leave the Replace with field empty.
• Then press Replace All.

• As a result, we will get the below output. All the specified unwanted part from the text is trimmed as shown in the below screenshot.

Read More: [Fix] TRIM Function Not Working in Excel

2. Inserting Excel SUBSTITUTE Function to Trim Part of Text

This time, I will apply the SUBSTITUTE function in excel to trim a certain part from a text string. In this case, I am using the same dataset that was used in the previous method.

Steps:

• Type the following formula in Cell C5 and press Enter from the keyboard.

• Consequently, Excel will return the below result. Now, Use the Fill Handle (+) tool to copy the formula over the range C6:C10.

• In the end, here is the final output we will receive.

⏩ Note:

You can trim certain characters from text using the SUBSTITUTION function. You can delete multiple characters at once using this function.

3. Trimming Part of Text Using Flash Fill in Excel

You can use the Flash Fill option in excel to cut a certain part of a text string.  Excel can sense the patterns of the data you are entering into it. While trimming part of text using the Flash Fill option, this data sensing feature is applied. Let’s assume we have a dataset containing several people’s names along with their professions. Now, I will trim the name part from the below text strings.

Steps:

• Type the expected result in Cell C5 (next to the first cell of your dataset).
• Then start typing the expected result in the next cell too (here, Cell C6). Now excel will preview the output once it can sense the pattern of the entered data. To illustrate, as I have typed the Teacher in Cell C5 and started to type Engineer in Cell C6, excel understands I am looking for professions only.

• As the preview data appears, press Enter to get the below result.

4. Combining RIGHT & LEN Functions to Cut First Part of Text

We can trim the first part of a text string using excel formulas. Here I will use the RIGHT function along with the LEN function to cut the first two characters from the below dataset.

Steps:

• Type the below formula in Cell C5 and hit Enter.

• Finally, after applying the Fill Handle tool, this is the ultimate output.

Here, the LEN function returns the length of the text string of Cell B5. Then 2 is subtracted from the whole text’s length which returns 11.  After that, the RIGHT function extracts 11 characters from the right side of Cell B5.

5. Joining LEFT and LEN Functions to Trim Last Part of Text in Excel

Unlike the previous method, now I will cut the last part of a text string using the combination of LEFT and LEN functions. For instance, I will trim the last 5 characters from the text strings of the below dataset.

Steps:

• Firstly, type the following formula in Cell C5. Then press Enter.

• Upon entering the formula excel will return the below result. As you can see, the above formula has removed the last 5 characters from all the text strings.

Here, the LEN function returns the total length of Cell B5. Next, 5 is subtracted from the LEN formula and replies 11. Finally, the LEFT function returns the 11 characters from the left side of the text string of Cell B5.

⏩ Note:

You can wrap the above formula with the VALUE function if you need a numeric result.

6. Merging MID & LEN Functions to Cut both First N and Last N Characters

In this method, I will trim the first N and last N characters from a text string using the MID function along with the LEN functions. To illustrate, I will delete the first 2 and last 5 characters from the text strings of the dataset below.

Steps:

• Type the below formula in Cell C5 at first and hit Enter.

• Once you hit Enter and apply the Fill Handle tool, excel will return the below result. From the above result, we can see that the first 2 and last 5 characters from each string are trimmed as displayed in the below screenshot.

Here, the LEN function returns the length of Cell B5 which is 18. Then the total number of characters (here, 2 + 5)  that are to be trimmed is subtracted from the total length of Cell B5 (here, 18). The subtraction results in 11. Then the MID function extracts 11 characters from the 3rd position of the text string of Cell B5.

7. Cutting Part of Text before or after Specific Character

You can trim text before or after a specific character (comma, semicolon, space, etc.) using Excel formulas. Suppose, we have the below dataset that contains text strings that are separated by a comma. Now I will apply Excel functions to remove everything before/after a comma.

7.1. Trimming Part of Text before Specific Character

First I will cut the part of the text that is placed before a comma.

Steps:

• Type the following formula in cell C5. Next press Enter.

• After entering the formula here is the result we receive. We can see that all the characters before the comma are trimmed.

Here, the SEARCH function finds the location of the comma in the given text string of Cell B5, which is 7. Then 7 is subtracted from the length of Cell B5, returned by the LEN function. The result of subtraction is 8. Finally, the RIGHT function trims 8 characters from the right side of the comma.

Read More: Trim Right Characters and Spaces in Excel

7.2. Trimming Part of Text after Specific Character

Likewise the previous method, here I will trim part of the text that is located after a comma.

Steps:

• Type the below formula in Cell C5 and hit Enter.

• Upon entering the formula we can see all the parts of the text string that are located after commas are removed.

Here, the SEARCH function finds the location of the comma. Next, 1 is subtracted from the SEARCH formula as we do not want to include a comma in our final result. In the end, the LEFT function extracts the text part before the comma. Thus we trimmed the text part after the comma.

⏩ Note:

You can trim part of the text before/after the occurrence of specific characters (comma, semicolon, space, etc.) in different positions using a combination of excel functions.

8. Applying Excel REPLACE Function to Trim Part of Text

Now I will use the REPLACE function in excel to trim part of text strings. For instance, from the below dataset, I will trim all the names.

Steps:

• Type the below formula in Cell C5. Then press Enter.

• As a consequence, excel will return the below result. From the below result we can see that the name portions from the below text strings are trimmed.

9. Using VBA to Trim First or Last Part of Text

We can trim part of the text using simple VBA code in Excel. I will use VBA User Defined Function to cut a portion from text strings.

9.1. Cutting First Part of Text Strings

First I will delete the first two characters using VBA UDF. Consider the below dataset to trim the first 2 characters.

Follow the below steps to perform the task.

Steps:

• Firstly, go to Developer > Visual Basic.

• As a result, the VBA window will appear. Right-click on the VBAProject, and go to Insert > Module.

• Now type the below code in the Module.

Public Function TrimFirstn(range As String, count As Long)
TrimFirstn = Right(range, Len(range) - count)
End Function

• Then go to the excel sheet where you have the data, and start to type the function you have created using VBA. It will appear like other excel functions.

• After that, enter the arguments of the function which will look like the below formula:

• Press Enter and apply the Fill Handle tool to copy the formula to the rest of the cells. Finally, you will get the below result.

9.2. Trimming Last Part of Text

Now I will use the VBA UDF to trim the last part of a text string. This method is similar to the previous method, just you have to type a different VBA code. For example, I will use the last 5 characters from the below text strings.

Steps:

• Likewise, in the previous method, go to Developer > Visual Basic. Then Insert a new Module from the VBAProject and type the below code in the Module (see screenshot).

Public Function TrimLastn(range As String, count As Long)
TrimLastn = Left(range, Len(range) - count)
End Function

• Now enter the newly created UDF and insert the arguments as below:

• Once you enter the formula, excel will trim the last 5 characters from the given text strings.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Conclusion

In the above article, I have tried to discuss several methods to trim part of a text in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

Related Article

• How to Trim Spaces in Excel
• How to Use Left Trim Function in Excel 

<< Go Back to Excel TRIM Function | Excel Functions | Learn Excel