Remove text by variable position

While working with Excel, we are able to retrieve the specific parts of a text string that we want by using the REPLACE function.  However, when the position of the text we want to obtain varies, we make use of the FIND  function.  This step by step tutorial will assist all levels of Excel users in removing text or characters by variable position.  

Figure 1. Final result: Remove text by variable position

Final formula:  =REPLACE(B3,1,FIND("/",B3),"")

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 FIND function

FIND locates and returns the starting position of a substring within a text string; FIND is case-sensitive

=FIND(find_text, within_text, [start_num])

The parameters are:

• find_text – the text we want to find in the second text string    
• within_text –  the text string containing the text we want to find    
• start_num – Optional; the starting character where we want to start the search; if omitted, default value is 1, which is the first character of within_text  

Setting up Our Data

Our table contains two columns: Product ID (column B) and Variety (column C).  Each Product ID contains the variety at the last part, right after the slash “/” key.  We want to remove the other text in Product ID 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 variable position

Remove text by variable position

We want to obtain only the variety from each Product ID in column B.  Note that the length of the Product IDs is not uniform, and the position of the variety in each ID is not constant.  

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

Step 1.  Select cell C3

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

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 Product ID in column B, while start_num is 1.  Num_chars is determined by the FIND function: FIND("/",B3).  It simply locates the slash key “/” and returns its position in cell B3.  As a result, it returns “8”, because the “/” is the eighth character of B3 “998-ERA/BAP”.  

Our REPLACE function then replaces the unwanted characters in B3 with an empty string “”, starting from the first character up to the eighth character.  

Finally, the first eight characters are removed, leaving the substring “BAP” in cell C3.  

Figure 3. Entering the formula using REPLACE and FIND

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

Figure 4. Output: Remove text by variable position

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.