How do you remove part of a formula

Macer · #1 09-23-2015, 11:28 AM

The basic goal is to remove part of a formula so that the new formula is looking at the correct cell. For example, I want to get the formula

='C:\filepath\[filename]Sheet1'!A4

To be

='Sheet1'!A4

So I am basically trying to remove the file name from the cell. So that it will look at the sheet in the current file rather than the previous one. The issue is that the file path or name won't always be the same. I have tried to use the I have tried to use Filter and Replace mixed with wildcards, but no luck.

NoSparks · #2 09-23-2015, 01:06 PM

Perhaps something like this ?

Code:

Sub RemovePathAndFileName()

    Dim str As String
    
str = Range("A2").Formula
MsgBox "'" & Mid(str, InStrRev(str, "]") + 1)

End Sub

Macer · #3 09-24-2015, 11:08 AM

Thanks that helped.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to colour this part?	mindfire	Project	1	11-17-2014 06:07 PM
Changing a part of vba code	Milade8080	Excel Programming	2	09-11-2014 10:34 PM
Searching for part, BUT NOT ALL, of a word...	MikeWebster2000	Outlook	2	09-27-2013 06:35 AM
Need an excel formula to remove dashes in a number sequence	jyfuller	Excel Programming	3	02-27-2013 10:32 PM
Print Part of a Slide	Cpt Black	PowerPoint	0	12-12-2008 10:30 AM