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\","")
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.