Summary

To remove text from a cell by position, you can use the REPLACE function. In the example shown, the formula in C6 is:

=REPLACE(B6,1,24,"")

which replaces the first 24 characters in the text with an empty string ("").

Generic formula

=REPLACE(text,start,characters,"")

Explanation 

The replace function lets you replace text based on its location and length. In this case, we want to strip off the drive and path and leave only the document name. The length of this part of the string (text) is 24 and the starting position is 1, and the pattern never changes.

The REPLACE function can handle this easily, we just need to provide a cell reference (B6), a starting position (1), the number of characters to replace (24), and the text to use for the replacement (""):

=REPLACE(B6,1,24,"")

For the replacement, we use an empty string ("") which causes REPLACE to effectively remove characters 1-24.

Alternative with SUBSTITUTE

Since the text in this case never varies, we could also use the SUBSTITUTE function to perform the name operation:

=SUBSTITUTE(B6,"C:\Users\dave\Documents\","")

Related formulas

Excel formula: Split text string at specific character

Split text string at specific character

In this example, the goal is to split a text string at the underscore("_") character with a formula. Notice the location of the underscore is different in each row. This means the formula needs to locate the position of the underscore character first before any text is extracted. There are two...
Excel formula: Remove characters from right

Remove characters from right

The LEFT function is perfect for extracting characters starting from the left side of a text string. We use LEFT in this formula to extract all characters up to the number of characters we want to trim. The challenge, for values with variable length, is that we don't know exactly how many...
Excel formula: Remove first character

Remove first character

This formula uses the REPLACE function to replace the first character in a cell with an empty string (""). The arguments for REPLACE are configured as follows: old_text is the original value from column B start_num is hardcoded as the number 1 num_chars comes from column C new_text is entered as an...
Excel formula: Remove text by variable position

Remove text by variable position

The REPLACE function will replace text by position. You can use REPLACE to remove text by providing an empty string ("") for the "new_text" argument. In this case, we want to remove the labels that appear inside text. The labels vary in length, and include words like "Make", "Model", "Fuel economy...
Excel formula: Remove text by matching

Remove text by matching

The SUBSTITUTE function lets you replace text by matching content. In this case, we want to remove hyphens from telephone numbers. The SUBSTITUTE function can handle this easily — we just need to provide a cell reference (B6), the text to remove ("-"), and the an empty string ("") for replacement...
Excel formula: Remove last word

Remove last word

This formula uses the MID function to remove the last word from a text string. The main challenge is to figure out where the last word begins. The formula is a bit convoluted, but the steps are simple. We first count how many spaces exist in the text using LEN and SUBSTITUTE. Next, we use the...

Related functions

Excel REPLACE function

REPLACE Function

The Excel REPLACE function replaces characters specified by location in a given text string with another text string. For example =REPLACE("XYZ123",4,3,"456") returns "XYZ456".
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions