SUBSTITUTE function replaces old text with a new one. LEN function returns the number of characters in the text string.
#Vba get file path from filename full#
To extract the path from the full path and file name, firstly, the formula counts the number of character “\” by the LEN and SUBSTITUTE functions, then replace the last “\” with a special character “?” by the SUBSTITUTE function, finally, find the special character “?” and extract the path by using the FIND and LEFT functions. Press Enter key to extract the path from the cell. Take an instance: to extract the path from cell B3, which contains full path and the file name, please use below formula: =LEFT(B3,FIND("?",SUBSTITUTE(B3,"\","?",LEN(B3)-LEN(SUBSTITUTE(B3,"\",""))))) Generic formula: LEFT( path,FIND("?",SUBSTITUTE( path,"\","?",LEN( path)-LEN(SUBSTITUTE( path,"\","")))))Īrguments Path: the cell reference or text string contains file path and file name. The formula is a little long, but this tutorial will explain how the formula works for you. If you want to extract the path from full path and file name, you can use a formula which combines the LEFT, FIND, SUBSTITUTE and LEN functions to handle it. Lr = sht.Cells(, "A").End(xlUp).Excel Formula Extract path from file name
#Vba get file path from filename code#
Here you can see a sample screenshot of some VBA code that I used in a project in order to quickly get only the text after the last “|” character in a given range of cells: PS: If you would like to do this with VBA, there is a nice function called INSTRREV which returns the position of the first occurrence of a string in another string, starting from the end of the string.
If we combine all above formulas into one we can use the following: Then, we can simply find out the position of the “$” in the new string using the Find or Search functions and get the file name with the Right function (or Mid would work as well): We can use it in order to replace the 7th “/” character (in our example) with some other unique character (for example the “$” will do the work): The excel Substitute function has an optional parameter named “instance_num” that can be used to substitute only the desired instance number of a character in a string. Now that we know the number of last occurrence of the “/” character in our string, we can replace this with another character in order to be able to distinguish it from the other “/” characters that we have in the same string. So, we can do this by using the Excel Substitute function: The total number of “/” is also the last occurrence of this character in the string 🙂 Then, the length of the initial string minus the length of the string without “/” characters will give us the number of the “/” characters in the initial string. We can get the last occurrence of the “/” character by replacing all the “/” characters in the initial string with nothing. So if we will somehow be able to find the position of the last “/” character in the initial string we could easily get the file name by using something like: =Right(“initial string”,len(“initial string”)-“position of last ‘/’ in the string”).
The file name is the sub-string after the last occurrence of the “/” character in the initial string and until the end. One formula that can solve your problem is this one: You have an Excel file with thousands of rows like in the below image and for each row you need to extract the file name:
0 kommentar(er)
