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\","")
Explanation
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”, and so on. Each label is followed by a colon and a space. We can use the colon as a “marker” to figure out where the label ends.
Working from the inside out, we use the FIND function to get the position of the colon in the text, then add 1 to take into account the space that follows the colon. The result (a number) is plugged into the REPLACE function for the “num_chars” argument, which represents the number of characters to replace.
The REPLACE function then replaces the text from 1 to “colon + 1” with an empty string (""). In the example shown, the solution looks like this:
=REPLACE(B6,1,FIND(":",B6)+1,"")
=REPLACE(B6,1,6,"")
=2016
