How to Remove Prefix and Suffix in Excel [11 Methods]

ByRhidi Barma Updated on Excel Formulas

A prefix and a suffix are specific sequences of characters that appear at the beginning (prefix) or the end (suffix) of a string or word. They are often used to add context, information, or structure to text data. Removing prefixes and suffixes in Excel involves eliminating specific text that appears at the beginning (prefix) or end (suffix) of a cell’s content, leaving only the core or desired portion of the text. In this article, I will show you 11 different methods to remove prefixes and suffixes in Excel.

Case 1: Remove Prefix or Suffix Separately

Removing prefixes and suffixes from strings in Excel is a common task when you need to clean or extract specific information from your data. In this type of case first we try to remove prefixes and then we try to remove suffixes.

Remove Prefix and Suffix Separately by Applying the Find and Replace Option

The Find and Replace command may come in handy to remove specific characters in some special circumstances. It is the easiest method to remove suffixes and prefixes in Excel.

To remove prefixes, apply the Find and Replace option. The process is given below:

  1. First, choose cell (C2:C10) and press CTRL+C to copy the selected texts.Find and Replace Copying for Prefix Removing
  2. Then choose cell (D2:D10) and press CTRL+V to paste those selected texts.Find and Replace Pasting for Prefix Removing
  3. After pasting, again choose cell (D2:D10) and press CTRL+F to open the Find and Replace dialog box.Find and Replace Finding for Prefix Removing
  4. In the Find and Replace dialog box, type “STU” in the “Find What” box and leave the “Replace With” box blank.
  5. Finish the process after clicking the “Replace All” button and a pop-up message will confirm all the replacements.Find and replace tab

Final Result for Removing Prefix

After that, we can see that all prefixes are removed.

Final result for removing prefix by find and replace method

Utilize the Find and Replace function to get rid of suffix. The steps are listed below:

  1. First, choose cell (C2:C10) and press CTRL+C to copy the selected texts.
  2. Then choose cell (D2:D10) and press CTRL+V to paste those selected texts.Find and replace copy and paste for removing suffix
  3. After pasting, again choose cell (E2:E10) and press CTRL+F to open the Find and Replace dialog box.Finding for removing suffix
  4. In the Find and Replace dialog box, type “-NSU” in the “Find What” box and leave the “Replace With” box blank.
  5. Finish the process after clicking the “Replace All” button and a pop-up message will confirm all the replacements.Find and Replace window

Final Result for Removing Suffix

Here we can see that all Suffixes are removed.

Final result for removing suffix for find and Replace

Remove  Prefix and Suffix Separately Using Flash Fill Feature

Flash Fill is a feature in Microsoft Excel that helps you quickly clean, format, or transform data in a column without writing complex formulas or using manual data manipulation techniques. It automatically detects patterns in your data and fills in values or extracts information accordingly. However, the Flash Fill feature is available only in Microsoft Excel 2013 and later versions.

Use the Flash Fill feature and remove the prefix. Here’s how:

  1. Select a cell (D2) and type 44297-NSU manually without typing the prefix.Manual typing to remove prefix in Flash Fill option
  2. Then select the next cell (D3).
  3. Go to Data.
  4. After that select Flash Fill from the Data Tools group. Or, click CTRL+E. Selecting Flash Fill option

Final Result for Removing Prefix

After applying Flash Fill we can see that the prefixes from the Dataset have been removed.

Final result for removing prefix in Flash Fill method

Utilize the Flash Fill function to eliminate the suffix. Here’s how to do it:

  1. Select a cell (E2) and type STU44297 manually without typing the prefix.Manual typing to remove suffix in Flash Fill option
  2. Then select the next cell (E3).
  3. Go to Data.
  4. After that select Flash Fill from the Data Tools group. Or, click CTRL+E. Selecting Flash Fill option

Final Result for Removing Prefix

Finally, we can see that Flash Fill removes the Suffixes from the Dataset.

Final result for removing suffix in Flash Fill method

Remove Prefix and Suffix Separately Using RIGHT & LEN and LEFT & LEN Functions

One of the easiest ways to remove the last digit in Excel is by using the RIGHT-LEN and LEFT-LEN combinations. The LEFT function removes a specified number of characters from the left side of a string on the other hand, the RIGHT function removes the number of characters from the right side of a string. Now follow the steps below to see how it works.

Syntax for Removing Prefix

=RIGHT(text, LEN(text)-1)

Remove Prefix Formula

=RIGHT(C2, LEN(C2)-3)

Syntax of Removing Suffix

=LEFT(text, LEN(text)-1)

Formula to Remove Suffix

=LEFT(C2,LEN(C2)-4)

Formula Breakdown for Removing Prefix

  • C2: It is the cell reference. This is the cell from which you want to extract the text.
  • LEN(C2): Calculates the length (number of characters) of the text in cell C2.
  • LEN(C2)-3: Subtracts 3 from the length calculated in the previous step.
  • RIGHT(C2, LEN(C2)-3): Takes the rightmost characters from cell C2. It extracts as many characters as specified by the result of LEN(C2)-3.

The LEFT & LEN functions also work in the same way.

To remove prefixes, use the RIGHT & LEN functions. Here’s how:

  1. Copy the RIGHT & LEN combination formula above.
  2. Apply the formula in cell C2.Right and LEN functions to remove prefix
  3. Drag the Fill Handle from cell D2 to D10.Dragging down RIGHT and LEN function

Final Result for Removing Prefix

After applying RIGHT & LEN we can see that the prefixes from the dataset have been removed.

Final result for RIGHT and LEN function to remove prefix

To remove suffixes, use LEFT & LEN functions. Here’s how:

  1. Copy the LEFT & LEN combination formula above.
  2. Apply the formula in cell E2.LEFT and LEN functions to remove suffix
  3. Drag the Fill Handle from cell E2 to E10.Dragging down LEFT and LEN function

Final Result for Removing Suffix

Here all suffixes from every cell have been removed by using the LEFT & LEN Functions.

Final result for LEFT and LEN function to remove suffix

Remove Prefix and Suffix Separately by Using REPLACE Function

The REPLACE function is used to replace a specified number of characters in a text string with new text. It is commonly used for text manipulation and data-cleaning tasks. The methods below will show you how to replace suffixes and prefixes in Excel using the REPLACE function.

Syntax

= REPLACE(old_text, start_num, num_chars, new_text)

Formula for Removing Prefix

=REPLACE(C2,1,3,"")

Formula for Removing Suffix

=REPLACE(C2,9,4,"")

Formula Breakdown

  • This formula is using the REPLACE function in Excel.
  • It operates on the cell C2. The function takes four arguments: the original text (C2), the starting position (1), the number of characters to replace (3), and the replacement text (“” – an empty string).
  • In essence, it removes the first three characters from the content of cell C2 and replaces them with nothing, effectively deleting them.

The formula for removing suffixes also works like this.

You can also insert the REPLACE function to remove prefixes, by following the steps below:

  1. Copy the formula for removing prefixes.
  2. Apply the formula in cell D2.REPLACE functions to remove prefix
  3. Drag the Fill Handle from D2 to D10.Dragging down REPLACE function

Final Result for Removing Prefix

After that, all prefixes from every cell have been removed by using the REPLACE Function.

Final result for REPLACE function to remove prefix

You can also insert the REPLACE function to remove suffixes, by following the steps below:

  1. Copy the formula for removing prefixes.
  2. Apply the formula in cell E2.REPLACE functions to remove suffix
  3. Drag the Fill Handle from E2 to E10.Dragging down REPLACE function

Final Result for Removing Suffix

Here we see that all suffixes from every cell have been removed by using the REPLACE Function.

Final result for REPLACE function to remove suffix

Remove Prefix and Suffix Separately by Applying SUBSTITUTION Function

An alternative way to remove the special character is using a SUBSTITUTE function. It is used to replace a character with another. The dataset for this SUBSTITUTE function is given below.

Dataset for substitution function

Syntax

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(text,old_text,new_text),old_text,new_text),old_text,new_text)

Formula for Removing Prefix

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"S",""),"T",""),"U","")

Removing Suffix Formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"B",""),"Y",""),"D",""),"-","")

Formula Explanation

This formula is a nested use of the SUBSTITUTE function in Excel, applied to the content of cell C2. It replaces occurrences of the letters “S,” “T,” and “U” with an empty string (“”), effectively removing these letters from the original text. The nested structure indicates sequential substitution, where each inner SUBSTITUTE function operates on the result of the previous one. In summary, this formula transforms the content of cell C2 by eliminating occurrences of the specified letters “S,” “T,” and “U.” 

Follow the steps below to remove the prefix by using the SUBSTITUTE function:

  1. Copy the formula for removing prefixes.
  2. Apply the formula in cell D2.SUBSTITUTE functions to remove prefix
  3. Drag the Fill Handle from D2 to D10.Dragging down SUBSTITUTE function

Final Result for Removing Prefix

As a result, all prefixes from every cell have been removed.

Final result for SUBSTITUTE function to remove prefix

To use the SUBSTITUTE function to remove a suffix, follow these steps:

  1. Copy the formula for removing suffixes.
  2. Apply the formula in cell E2.SUBSTITUTE functions to remove suffix
  3. Drag the Fill Handle from E2 to E10.Dragging down SUBSTITUTE function

Final Result for Removing Suffix

Here we see that the SUBSTITUTE Function removes all suffixes from every cell.

Final result for SUBSTITUTE function to remove suffix

Applying VBA code to Remove Prefix and Suffix Separately

Another approach is to use VBA code to remove special characters from Excel. VBA (Visual Basic for Applications) in Excel is a programming language that allows you to automate tasks, create custom functions, and build applications within Microsoft Excel.

Apply VBA code and remove the prefix. Here’s how:

  1. Go to Developer and select Visual Basic or press ALT+F11.
  2. This will open the Visual Basic Editor.VBA code developer
  3. Then select Insert and create a Module.Module Insertion
  4. Copy the code below and paste it into the newly created module.
    Sub RemovePrefix()
Dim inputRange As Range
Dim outputRange As Range
Dim prefixDigits As Integer
Dim cell As Range
Dim inputCell As Range
Dim result As String

' Prompt the user to select the input range
On Error Resume Next
Set inputRange = Application.InputBox("Select the range containing text with prefix:", Type:=8)
On Error GoTo 0

' Exit if no input range is selected
If inputRange Is Nothing Then
     Exit Sub
End If

' Prompt the user to select the output range
On Error Resume Next
Set outputRange = Application.InputBox("Select a single cell where you want to start placing the modified text:", Type:=8)
On Error GoTo 0

' Exit if no output range is selected
If outputRange Is Nothing Then
     Exit Sub
End If
' Prompt the user for the number of digits in the prefix
prefixDigits = InputBox("Enter the number of digits in the prefix:")

    

' Loop through each cell in the input range
For Each inputCell In inputRange
     If Not IsEmpty(inputCell.Value) Then ' Check if the cell is not empty
         result = inputCell.Value
         ' Remove the prefix based on the specified number of digits
         If Len(result) >= prefixDigits Then
             result = Mid(result, prefixDigits + 1)
         End If
         ' Place the modified text in the corresponding cell of the output range
         outputRange.Value = result
         ' Move to the next cell in the output range
         Set outputRange = outputRange.Offset(1, 0)
     End If

Next inputCell
End Sub
  5. Save the code by pressing CTRL+S.
  6. Go to Developer and click the Macros button or you can easily open the Macro dialog box by pressing ALT+F8.Macros insertation
  7. Click Run by selecting RemovePrefix.MACROS windows
  8. Select the range C2:C10 and click OK.Input range selection
  9. Select cell D2 and click OK.output cell selection
  10. Enter the number of digits in the prefix and click OK.number of digit selection for prefix

Final Result for Removing Prefix

By doing these steps we can remove the prefix.

Final result for removing prefix using VBA code

Run the VBA code to remove the suffix. Here’s how to do it:

  1. Press ALT+F11 to open the Visual Basic Editor.
  2. Then a new window will be opened and select Insert and Create a Module.Module insertion
  3. Copy the code below and paste it.
    Sub RemoveSuffix()
Dim inputRange As Range
Dim outputRange As Range
Dim suffixDigits As Integer
Dim cell As Range
Dim inputCell As Range
Dim result As String

' Prompt the user to select the input range
On Error Resume Next
Set inputRange = Application.InputBox("Select the range containing text with suffix:", Type:=8)
On Error GoTo 0

' Exit if no input range is selected
If inputRange Is Nothing Then
     Exit Sub
End If

' Prompt the user to select the output range
On Error Resume Next
Set outputRange = Application.InputBox("Select a single cell where you want to start placing the modified text:", Type:=8)
On Error GoTo 0

' Exit if no output range is selected
If outputRange Is Nothing Then
     Exit Sub
End If

' Prompt the user for the number of digits in the suffix
suffixDigits = InputBox("Enter the number of digits in the suffix:")
' Loop through each cell in the input range
For Each inputCell In inputRange
     If Not IsEmpty(inputCell.Value) Then ' Check if the cell is not empty
         result = inputCell.Value
         ' Remove the suffix based on the specified number of digits
         If Len(result) >= suffixDigits Then
             result = Left(result, Len(result) - suffixDigits)
         End If
         ' Place the modified text in the corresponding cell of the output range
         outputRange.Value = result
         ' Move to the next cell in the output range
         Set outputRange = outputRange.Offset(1, 0)
     End If
Next inputCell
End Sub
  4. Save the code by pressing CTRL+S.
  5. Press ALT+F8 to open the Macro dialog box.
  6. Click Run after selecting RemoveSuffix.Macros windows
  7. Select the range C2:C10 and click OK.Input range selection for suffix
  8. Select cell E2 and click OK.output cell selection
  9. Enter 4 as the number of digits in the suffix and click OK.\Number of digits in suffix

Final Result for Removing Suffix

Finally, we see that the suffix has been removed.

Final result for removing suffix by VBA code

Case 2: Remove Prefix and Suffix Simultaneously

When you need to clean up or extract particular information from your data, removing prefixes and suffixes from strings in Excel is a regular operation. Here we discussed how we could remove prefixes and suffixes simultaneously.

Applying Flash Fill Tool to Remove Prefix and Suffix Simultaneously

We can use the Flash Fill feature to remove both suffixes and prefixes instantly. It is the easiest method.

Apply the Flash Fill tool and remove prefixes and suffixes simultaneously. Follow the steps below:

  1. Select a cell (D2) and type 44297 manually without typing the prefix and Suffix.Manually typing to removing suffix and prefix simultaneously by Flash FILL method
  2. Then select the next cell (D3).
  3. Go to Data.
  4. After that select Flash Fill from the Data Tools group. Or, click CTRL+E. Selecting Flash Fill option

Final Result 

After applying Flash Fill we can see that the prefixes and suffixes from the dataset have been removed.

Final result for Flash Fill option to remove prefix and suffix simultaneously

Applying the Find and Replace Option to Remove Prefix and Suffix Simultaneously

The Find and Replace command can be used to remove suffixes and prefixes in some special circumstances.

Follow these steps to remove prefixes and suffixes simultaneously by applying the Find and Replace option:

  1. First, choose cell (C2:C10) and press CTRL+C to copy the selected texts.
  2. Then choose cell (D2:D10) and press CTRL+V to paste those selected texts.Copy and Paste
  3. Choose cell (D2:D10) and press CTRL+F to open the Find and Replace dialog box.Finding for prefix and suffix
  4. In the Find and Replace dialog box, type “STU” in the “Find What” box and leave the “Replace With” box blank.
  5. Finish the process after clicking the “Replace All” button and a pop-up message will confirm all the replacements.Find and Replace window
  6. First, choose cell (D2:D10) and press CTRL+C to copy the selected texts.
  7. Then choose cell (E2:E10) and press CTRL+V to paste those selected texts.Copy and Paste
  8. Choose cell (E2:E10) and press CTRL+F to open the Find and Replace dialog box.CTRL+F
  9. In the Find and Replace dialog box, type “-NSU” in the “Find What” box and leave the “Replace With” box blank.
  10. Finish the process after clicking the “Replace All” button and a pop-up message will confirm all the replacements.Find and Replace Window

Final Result

Here all the prefixes and suffixes are removed.

Final result for Find and Replace to remove prefix and suffix simultaneously

Using MID and LEN Functions to Remove Prefix and Suffix Simultaneously

Here, we’ll use Excel’s MID and LEN functions to get rid of the prefixes and suffixes. We are using these two functions to eliminate the first three and last four characters from our dataset. Please follow the instructions below to remove the prefixes using the MID and LEN functions.

Syntax

=MID(text, LEN(text)-1)

Formula

=MID(C2, LEN("STU") + 1, LEN(C2) - LEN("STU") - LEN("-NSU"))

Formula Breakdown

This formula utilizes the MID function in Excel to extract a substring from the content of cell C2.

  • The MID function takes three arguments: the original text (C2), the starting position (calculated as the length of “STU” plus 1), and the number of characters to extract (calculated as the length of C2 minus the combined length of “STU” and “-NSU”).
  • In essence, this formula extracts a portion of the text in cell C2, starting from the position immediately following “STU” and ending just before the characters “-NSU.” The result is a substring without “STU” and its preceding hyphen.

Use MID and LEN functions to remove prefixes and suffixes simultaneously. Follow these steps:

  1. Copy the MID & LEN formula above.
  2. Paste the formula in cell C2.MID and LEN functions to remove prefix and suffix simultaneously
  3. Drag the Fill Handle from cell D2 to D10.Dragging down MID and LEN function

Final Result

Here we see, that all the prefixes and suffixes are removed.

Final result for MID and LEN function to remove prefix and suffix simultaneously

Applying the Nested SUBSTITUTE Function to Remove Prefix and Suffix Simultaneously

One of the common methods to remove prefixes and suffixes is the SUBSTITUTE formula. Here it is given below:

Syntax

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(text,old_text,new_text),old_text,new_text),old_text,new_text)

Formula 

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"S",""),"T",""),"U",""),"B",""),"Y",""),"D",""),"-",""),"N","")

Formula Breakdown

  • This Excel formula employs a series of nested SUBSTITUTE functions to manipulate the content of cell C2.
  • It systematically replaces occurrences of the letters “S,” “T,” “U,” “B,” “Y,” “D,” the hyphen “-“, and “N” with an empty string (“”), effectively removing these characters from the original text.

In summary, the formula progressively eliminates specified letters and the hyphen, transforming the content of cell C2 by iteratively substituting each targeted character with nothing.

To simultaneously eliminate the prefix and suffix, use the SUBSTITUTE function. Take these actions:

  1. Copy the above formula.
  2. Apply the formula in cell D2.SUBSTITUTE functions to remove prefix and suffix simultaneously
  3. Drag the Fill Handle from D2 to D10.Dragging down SUBSTITUTE function

Final Result 

Finally, the SUBSTITUTE Function removes all prefixes and suffixes from every cell.

Final result for SUBSTITUTE and LEN function to remove prefix and suffix simultaneously

Applying VBA Code to Remove Prefix and Suffix Simultaneously

The final method to remove prefixes and suffixes simultaneously is by applying VBA code.

Use VBA code to remove prefixes and suffixes simultaneously. The process is given below:

  1. Press ALT+F11 to open Visual Basic Editor.
  2. Then select Insert > Module.Module Insertion
  3. Copy the code below and paste it.
    Sub RemovePrefixAndSuffix()
Dim inputRange As Range
Dim outputRange As Range
Dim prefixDigits As Integer
Dim suffixDigits As Integer
Dim cell As Range
Dim inputCell As Range
Dim result As String
    
' Prompt the user to select the input range
On Error Resume Next
Set inputRange = Application.InputBox("Select the range containing text with prefix and suffix:", Type:=8)
On Error GoTo 0
    
' Exit if no input range is selected
If inputRange Is Nothing Then
     Exit Sub
End If

' Prompt the user to select the output range
On Error Resume Next
Set outputRange = Application.InputBox("Select a single cell where you want to start placing the modified text:", Type:=8)
On Error GoTo 0

' Exit if no output range is selected
If outputRange Is Nothing Then
     Exit Sub
End If

' Prompt the user for the number of digits in prefix and suffix
prefixDigits = InputBox("Enter the number of digits in the prefix:")
suffixDigits = InputBox("Enter the number of digits in the suffix:")

' Loop through each cell in the input range
For Each inputCell In inputRange
     If Not IsEmpty(inputCell.Value) Then ' Check if the cell is not empty
         result = inputCell.Value
         ' Remove the prefix and suffix based on the specified number of digits
         If Len(result) >= prefixDigits Then
             result = Mid(result, prefixDigits + 1)
         End If
         If Len(result) >= suffixDigits Then
             result = Left(result, Len(result) - suffixDigits)
         End If
         ' Place the modified text in the corresponding cell of the output range
         outputRange.Value = result
         ' Move to the next cell in the output range
         Set outputRange = outputRange.Offset(1, 0)
     End If
Next inputCell
End Sub
  4. Save the module by pressing CTRL+S.
  5. Now press ALT+F8 to open the Macro dialog box.
  6. Select RemovePrefixSuffix and hit the Run button.MACROS window
  7. Select the range C2:C10 and click OK.Selection of input range
  8. Select cell D2 and click OK.selection of output cell
  9. Enter 3 as the number of digits in the prefix and click OK.Number of digits in prefix
  10. Enter 4 as the number of digits in the suffix and click OK.Number of digits in suffix

Final Result 

Finally, we see that the prefix and suffix have been removed.

Final result for VBA method to remove prefix and suffix simultaneously

Conclusion

In this article, I’ve discussed two types of cases to remove prefixes and suffixes. One case is separately removing prefixes and suffixes and another is removing prefixes and suffixes at the same time. I have used Flash Fill, Find and Replace, SUBSTITUTE, RIGHT, LEFT, and  LEN functions. By following these methods you can easily remove prefixes and suffixes in Excel.

Frequently Asked Questions (FAQs)

How do I remove prefix and suffix spaces in Excel?

To remove both prefix and suffix spaces in Excel, you can use the TRIM function.

  1. Simply apply the formula as follows: =TRIM(A1)
  2. Replace “A1” with the reference to the cell containing the text with spaces.
  3. This formula trims leading and trailing spaces, ensuring that only the content without any leading or trailing spaces is displayed.

How do I remove the prefix 00 in Excel?

To remove the prefix “00” from a cell in Excel, you can use the SUBSTITUTE function.

  1. Assuming the data is in cell A1, you can use the following formula: =IF(LEFT(A1,2)=”00″, RIGHT(A1, LEN(A1)-2), A1)
  2. This formula checks if the first two characters of the cell A1 are “00”.
  3. If they are, it removes them by taking the right part of the string starting from the third character.
  4. If the prefix is not present, it returns the original content of the cell. Adjust the cell references as needed based on your data.

How do I remove prefix currency in Excel?

To remove a currency prefix from a cell in Excel, you can use a combination of functions like SUBSTITUTE and VALUE.

  1. Assuming your currency values are in column A, you can use the following formula: =VALUE(SUBSTITUTE(A1,”$”,””))
  2. This formula assumes that the currency symbol is “$”. Replace “$” with the actual currency symbol used in your data.
  3. It uses the SUBSTITUTE function to replace the currency symbol with an empty string and then the VALUE function to convert the result into a numeric value.
  4. Adjust the cell references based on your data location.
5/5 - (2 votes)

Data Analyst

Howdy! I'm Rhidi Barma. I'm a professional Excel user. I've completed my graduation from Jahangirnagar University, Bangladesh. I've been working with Microsoft Excel since 2015. I love writing blogs on MS Excel tips & tricks, data analysis, business intelligence, capital market, etc. I love to spend my leisure time reading books and watching movies. To know more about me, you can follow me on social media platforms. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *