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.


Download Practice Workbook

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


9 Easy Methods to Trim Part of Text in Excel

1. Excel Find and Replace Option to Trim Part of Text

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.

Excel Find and Replace Option to Trim Part of Text

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.

Excel Find and Replace Option to Trim Part of Text

  • 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 Solutions


2. Use SUBSTITUTE Function to Cut Part of Text in Excel

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.
=SUBSTITUTE(B5,"Full Name:","")

Use SUBSTITUTE Function to Cut Part of Text in Excel

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

Use SUBSTITUTE Function to Cut Part of Text in Excel

  • 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. Trim Part of Text Using Flash Fill

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.

Trim Part of Text Using Flash Fill

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.

Trim Part of Text Using Flash Fill

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


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

Combine RIGHT & LEN Functions to Cut First Part of Text

Steps:

  • Type the below formula in Cell C5 and hit Enter.
=RIGHT(B5,LEN(B5)-2)

Combine RIGHT & LEN Functions to Cut First Part of Text

  • 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. Apply Excel Formula 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.

Apply Excel Formula to Trim Last Part of Text in Excel

Steps:

  • Firstly, type the following formula in Cell C5. Then press Enter.
=LEFT(B5,LEN(B5)-5)

Apply Excel Formula to Trim Last Part of Text in Excel

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

Combination of MID & LEN Functions to Cut both First N and Last N  Characters from Text

Steps:

  • Type the below formula in Cell C5 at first and hit Enter.
=MID(B5,3,LEN(B5)-7)

Combination of MID & LEN Functions to Cut both First N and Last N  Characters from Text

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

Cut Part of Text before or after Specific Character

7.1. Trim 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.
=RIGHT(B5,LEN(B5)-SEARCH(",",B5))

Trim Part of Text before Specific Character

  • 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 (5 Ways)


7.2. Trim 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.
=LEFT(B5,SEARCH(",",B5)-1)

Trim Part of Text after Specific Character

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

Read More: Left Trim Function in Excel: 7 Suitable Ways


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

Excel REPLACE Function to Trim Part of Text

Steps:

  • Type the below formula in Cell C5. Then press Enter.
=REPLACE(B5,1,13," ")

Excel REPLACE Function to Trim Part of Text

  • 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. Use VBA to Trim First or Last Part of Text in Excel

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. VBA to Cut 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.

VBA to Cut First Part of Text Strings

Follow the below steps to perform the task.

Steps:

  • Firstly, go to Developer > Visual Basic.

VBA to Cut First Part of Text Strings

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

VBA to Cut First Part of Text Strings

  • 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

VBA to Cut First Part of Text Strings

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

VBA to Cut First Part of Text Strings

  • After that, enter the arguments of the function which will look like the below formula:
=TrimFirstn(B5,2)

VBA to Cut First Part of Text Strings

  • 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. VBA to Trim 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.

VBA to Trim Last Part of Text

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

VBA to Trim Last Part of Text

  • Now enter the newly created UDF and insert the arguments as below:
=TrimLastn(B5,5)

VBA to Trim Last Part of Text

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


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

 

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo