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.
Leave a Comment