Remove text by position

While working with Excel, we are able to remove specific parts of a text string by using the REPLACE or SUBSTITUTE  functions.  This step by step tutorial will assist all levels of Excel users in removing text based on their position using two different methods.  

Figure 1. Final result: Remove text by position

Formula 1:  =REPLACE(B3,1,5,"")

Formula 2:  =SUBSTITUTE(B3,"9015-","")

Syntax of the REPLACE function

REPLACE function changes a specific part of a text string with a different text string, based on the number of characters we specify

=REPLACE(old_text, start_num, num_chars, new_text)

The parameters are:

• old_text  – any text that we want to change or modify
• start_num – the starting position of the character in old_text that we want to replace with new_text
• num_chars – the number of characters in old_text that we want to replace with new_text
• new_text – the text that will replace the characters in old_text

Syntax of the SUBSTITUTE function

SUBSTITUTE function replaces a word or text in a text string

=SUBSTITUTE(text, old_text, new_text, [instance_num])

• text  – the text string containing the word or text we want to replace or change
• old_text  – any text that we want to replace with new_text
• new_text  – the text we want to replace old_text with
• instance_num  – Optional; Specifies which occurrence of old_text we want to replace; If omitted, every occurrence of old_text in text is replaced with new_text

Setting up Our Data

Our table contains two columns: Batch Code (column B) and Variety (column C).  Each batch code contains the data for production date in Julian calendar, and the variety.  We want to remove the production date and obtain only the variety. The resulting text string will be recorded in column C under “Variety”.  

Figure 2. Sample data to remove text by position

Remove text by position using REPLACE

We want to obtain only the variety from each Batch Code in column B.  Note that the position of the variety is constant in each batch code.

In order to remove characters or text by position, we use the REPLACE function.  Let us follow these steps:

Step 1.  Select cell C3

Step 2.  Enter the formula: =REPLACE(B3,1,5,"")

Step 3:  Press ENTER

Step 4:  Copy the formula in cell C3 to cells C4:C6 by clicking the “+” icon at the bottom-right corner of cell C3 and dragging it down

Old_text is the batch code in column B, while start_num is 1 and num_chars is 5, corresponding to the number of characters we want to remove “9015-”.  Our formula replaces the unwanted characters with an empty string “”, starting from the first character up to the fifth character.  

As a result, the first five characters are removed, leaving the substring “BAP” in cell C3.  

Figure 3. Entering the formula using REPLACE

Copying the formula to the succeeding cells populates column C with the substrings we want as shown below.  

Figure 4. Output: Remove “9015-” by position using REPLACE

Remove text by position using SUBSTITUTE

There is an alternative solution to remove text or characters by position using the SUBSTITUTE function.  Let us follow these steps:

Step 1.  Select cell C3

Step 2.  Enter the formula: =SUBSTITUTE(B3,"9015-","")

Step 3:  Press ENTER

Step 4:  Copy the formula in cell C3 to cells C4:C6 by clicking the “+” icon at the bottom-right corner of cell C3 and dragging it down

Our formula directly substitutes the text string “9015-” with an empty string “”. As a result, we are able to obtain the variety in C3, which is “BAP”.  

The results are the same as in the previous method using REPLACE.  

Figure 5. Output: Remove “9015-” by position using SUBSTITUTE

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.