write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Sunday, 11 September 2011

Extract File name from path & file name

I have already posted how to find the content of folder with vb
if you you want to separate file name from file path & file name then use below formula

suppose your cell A1 contain C:\Users\MAHESH\Downloads\Data sample.xls
results will be Data sample.xls

  1.  Find total len of cell   LEN(A1) =41
  2. find Len of cell without "\" using SUBSTITUTE    LEN(SUBSTITUTE(A1,"\","") =37
  3. (1-2) will give u count of number of backslash  "\" i.e (41-37=4)
  4. we got last position of bracket is 4, but don’t know the string position in cell
  5. Find function always find the first instance
  6. Use the SUBSTITUTE function to change the last backslash
  7. FIND("*",SUBSTITUTE(A1,"\","*",4) will give you position of last backslash i.e 26
  8. we are replacing last back slash as * to get the position of last backslash
  9. we have taken 4 (number of backslash) as  instance_num in substitute formula
  10. Use Right function which will give you file name

You can try below DOS command to directory of the folder & sub folder
c:\User> Dir/s/b > F:\file.txt
this will save all the list of file (Directory) in F drive in file.txt you can use file.xls to save output as excel file

No comments:

Post a Comment