Learn how to remove text from a cell by position using the REPLACE and SUBSTITUTE and TEXTRIGHT formula in Excel. In this tutorial we’ll show you various string manipulation functions!
Formulas to remove text by position
Example 1: REPLACE
Let us see how the formula works!
To apply the REPLACE function, we need to add the following 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, “”)
To remove text by position from a cell, you can use the REPLACE function. In the example displayed, we’ll use the formula in B4:
The function will replace the text based on its position and length. For example, we want to extract the file name with extensions. The length of the matching string is 13. The starting position is 1.
As a result, this excel formula removes 13 characters starting from the first character.
Replace or remove? Hm, we are talking about the same thing. Why?
Good to know that if you are using an empty string as a replacement, the first 13 characters will be removed.
Remove Text by position using SUBSTITUTE
If you are using the same patterns in a range, for example, “Drive\directory\filename.ext” you can also use the SUBSTITUTE function.
Understanding the syntax is not rocket science:
Add this expression to formula bar:
and you will get the expected result.
Apply TEXTRIGHT function
Using the TEXTRIGHT function is a smart choice if you have to extract the matching string.
The function returns the last word from the source range using the specified delimiter. This a UDF (user-defined function); you can use it after installing our free add-in, which contains advanced formula libraries.
If you want to strip the filename from a cell, you have to use the following parameters.