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

Method 1 – Using Find and Replace to Trim a Part of Text in Excel

We have the dataset (B5:B10), containing a list of names. We will replace the text ‘Full Name:’ with a blank.

Steps:

• Select the dataset and press Ctrl + H to get the Find and Replace dialog.
• In the Find and Replace dialog, type the part of the text that you want to trim in the Find what field. We put “Full name: ” (with a space at the end).
• Leave the Replace with field empty.
• Press Replace All.

• 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

Method 2 – Inserting the SUBSTITUTE Function to a Trim Part of Text

Steps:

• Use the following formula in Cell C5 and press Enter.

• Use the Fill Handle (+) tool to copy the formula over the range C6:C10.

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

Method 3 – Trimming a Part of Text Using Flash Fill in Excel

We will trim the name from the below text strings.

Steps:

• Type the expected result in Cell C5 (next to the first cell of your dataset).
• Start typing the expected result in the next cell (here, Cell C6).
• Excel will preview the output once it can detect the pattern of the entered data. We have typed the Teacher in Cell C5 and started to type Engineer in Cell C6.

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

Method 4 – Combining RIGHT and LEN Functions to Cut the First Part of Text

We’ll cut the first two characters from the below dataset.

Steps:

• Use the following formula in Cell C5 and hit Enter.

• Apply the Fill Handle tool to copy the formula over the range.

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. The RIGHT function extracts 11 characters from the right side of Cell B5.

Method 5 – Joining LEFT and LEN Functions to Trim the Last Part of Text in Excel

We will trim the last 5 characters from the text strings of the below dataset.

Steps:

• Use the following formula in Cell C5.

• Press Enter.

• Use AutoFill on the column to copy the formula.

The LEN function returns the total length of Cell B5. Next, 5 is subtracted from the LEN result, which returns 11. 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.

Method 6 – Merging MID and LEN Functions to Cut both First and Last Characters

We will delete the first 2 and last 5 characters from the text strings of the dataset below.

Steps:

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

• Hit Enter and apply the Fill Handle tool.

The LEN function returns the length of Cell B5, which is 18. 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. The MID function extracts 11 characters from the 3rd (since we’re cutting the first two characters) position of the text string of Cell B5.

Method 7 – Cutting a Part of Text before or after a Specific Character

We have the below dataset that contains text strings that are separated by a comma. We will apply Excel functions to remove everything before or after a comma.

Case 7.1 – Trimming a Part of Text before a Specific Character

Steps:

• Use the following formula in cell C5 and press Enter.

• Use AutoFill.

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. The RIGHT function returns the 8 characters from the right side of the comma.

Read More: Trim Right Characters and Spaces in Excel

Case 7.2 – Trimming a Part of Text after Specific Character

Steps:

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

• Use AutoFill.

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. The LEFT function extracts the text part before 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.

Method 8 – Applying the REPLACE Function to Trim a Part of Text

From the following dataset, we will trim all the names.

Steps:

• Use the following formula in Cell C5 and press Enter.

• Use AutoFill.

Method 9 – Using VBA to Trim the First or the Last Part of Text

Case 9.1 – Cutting the First Part of Text Strings

Consider the below dataset to trim the first 2 characters.

Steps:

• Go to Developer and select Visual Basic.

• The VBA window will appear. Right-click on the VBAProject and go to Insert then to Module.

• Insert the following code in the Module.

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

• Go to the Excel sheet and insert the function you made via VBA. It will appear like other Excel functions.

• Here’s the formula you should use.

• Press Enter and apply the Fill Handle tool to copy the formula to the rest of the cells.

Case 9.2 – Trimming the Last Part of Text

We will remove the last 5 characters from the text strings.

Steps:

• Go to Developer and select Visual Basic.
• Insert a new Module from the VBAProject and paste the following code in the Module (see screenshot).

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

• Enter the newly created UDF and insert the arguments as below:

• Excel will trim the last 5 characters from the reference cell.
• Apply AutoFill to complete the column.

Download the Practice Workbook

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