Learn how to remove text from a cell by position in Excel using the REPLACE, SUBSTITUTE, and TEXTRIGHT functions.
Today’s tutorial is a part of our definitive guide on Excel Formulas.
Formulas to remove text by position
In Excel, there are many possibilities to perform string manipulations using built-in functions. We will introduce you to the most effective ways to remove or replace characters from a text string.
REPLACE function
The REPLACE function replaces a part of a text string with a different text string.
Syntax:
=REPLACE(old_text, start_text,text_length,new_text)Arguments:
The REPLACE function uses four required arguments:
- old_text: the cell reference
- start_text: this is the starting position
- text_lenght: the length of string to replace
- new_text: the replacement text (we’ll use an empty string, “”)
The following example uses the REPLACE function to remove text by position from a cell. For example, the formula in cell B3 is:
=REPLACE(B3,5,1,"")Explanation:
The formula will replace the text based on its position and length. For example, column B has strings containing the “*” character in various positions. Therefore, we want to extract the fruit names and replace the unwanted characters with an empty string.
In cell B3, we want to replace the “*” character in the 5th position. Therefore, the string length is 1 because we want to replace only one character.
Result:
REPLACE is a flexible function; if you look at cells B4 and B5, you can easily remove the string from their positions even if we have more than one character.
Remove text by position using SUBSTITUTE
You can also use the SUBSTITUTE function if you have the same patterns in a range.
Understanding the syntax is not rocket science:
SUBSTITUTE(text, old_text, new_text)
The formula in D3:
=SUBSTITUTE(B3, "*","")Extract the matching string using the TEXTRIGHT function
Use the TEXTRIGHT function if you have to extract the matching string. TEXTRIGHT is a user-defined function. TEXTRIGHT returns the last word from the source range using the specified delimiter. You can use TEXTRIGHT after installing our free add-in, which contains advanced formula libraries for Microsoft Excel.
Use the following configuration to strip the filename from a cell:
TEXTRIGHT(B3,"\")Result:
Related Formulas and Examples
