# Remove text by position

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 Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.