Remove text by position

Excel offers several functions to remove text. You can also remove text by position. You can either use the REPLACE or SUBSTITUTE function. In this tutorial, you will learn how to remove text by position in Excel.

How to Remove Text by Position in Excel

You can remove text by position using the REPLACE function. This function replaces a set of characters in a text with another string. The generic formula would look like:

Formula

=REPLACE (previous_text, start_num, num_chars, “”).

To replace text by the position, you need to provide the string you want to remove text form as previous_text. The start_num should be the position you want to start the search from. In the next step, you need to set the length of the text you want to remove from the original string. The last argument new_text needs to be an empty string (“”).

You can also use the SUBSTITUTE function to remove text by position. The formula would look like the following:

=SUBSTITUTE(text, text_to_remove, “”)

The SUBSTITUTE function works in the same way as REPLACE with a difference that you need to provide the path explicitly.

Examples

This example contains a little chunk from a product inventory data set. Column A has the full codes of the products having the product type, year and id concatenated together. In column B you will generate the ids which will remove the first 11 characters. To do this:

• Go to cell B2.
• Assign the formula, =REPLACE(A2,1,11,"") to cell B2.
• Press Enter to apply the formula to B2.
• Drag the fill handle from B2 to B6 to copy the formula to the whole column.
  
  

  1. Figure 1 of Remove text by Position with REPLACE

This will show the ids in column B removing the first part of the code.

To remove the text with the SUBSTITUTE function:

• Select cell B2 with your mouse.
• On the formula bar, write the formula,=SUBSTITUTE(A2, "Shoes-2018-", “”).
• Press Enter and drag the formula with your mouse from cell B2 to B6.
  
  

  1. Figure 2 of Remove Text by Position with SUBSTITUTE

This will show the same results as before.