Get parent folder path from file path using cell formula

by

This works.

=MID(A1,1,LEN(A1)-LEN(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))))

The above was my original answer. Neil simplified the expression somewhat and posted this as a comment below:

=LEFT(A1,FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

This takes advantage of the fact that ? is a forbidden character in paths so that "?" can safely be used instead of CHAR(1) as a placemark, thus improving readability a little bit. Also, LEFT(A1,x) is equivalent to, and shorter than MID(A1,1,x), so it makes sense to use LEFT. But most importantly, this formula makes use of FIND, instead of a second layer of counting characters using LEN. This makes it much more readable.

More Related Contents:

  1. How to turn a string formula into a “real” formula?
  2. Last non-empty cell in a column
  3. Excel formula to get cell color [duplicate]
  4. Rounding in MS Access
  5. Fill non-contiguous blank cells with the value from the cell above the first blank
  6. Excel Reference To Current Cell
  7. How to extract text within a string of text
  8. Format Cell Contents
  9. Excel formula checklist in employees
  10. How do I put double quotes in a string in vba?
  11. Excel VBA – How to add dynamic array formula
  12. Stop Excel from automatically converting certain text values to dates
  13. How can I URL encode a string in Excel VBA?
  14. VBA – Write all possible combinations of 4 columns of data [duplicate]
  15. Remove duplicates from array using VBA
  16. Count Unique values with a condition
  17. How to automatically insert a blank row after a group of data
  18. Get File Path (ends with folder)
  19. Best way to do Version Control for MS Excel [closed]
  20. Email a single attachment from folder of files each to a different person
  21. Excel error 1004 “Unable to get …. property of WorksheetFunction class” appearing inconsistently
  22. How to list all possible combinations of the values in three columns in excel?
  23. Sending object array to variant works before August 2019 Windows update, but fails after update
  24. How can I install/use “Scripting.FileSystemObject” in Excel 2010 for MAC?
  25. Excel VBA User Defined Function that counts cells with conditional formatting
  26. MS Excel – Concat with a delimiter
  27. Excel is calculating a formula with a VBA function as an error unless it is re-entered
  28. excel vba how to copy the value of multiple non-contiguous ranges into an array
  29. Check whether a cell contains a substring
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment