Explanation
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 text.
SUBSTITUTE will replace all instances of “-” with nothing.
Note that SUBSTITUTE is a case-sensitive function.
Removing more than one thing
If you need to remove more than one thing, you can nest multiple SUBSTITUTE functions. For example, to remove square brackets from text, you can use:
=SUBSTITUTE(SUBSTITUTE(text, "[", ""), "]", "")
You can nest at several levels, as described in this more complex formula to clean up telephone numbers .
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\","")
