Excel formula to extract folder from file path

You can use three Excel formulas to extract a folder from a file path.

In this example we have file paths listed in column A.

1. In column B enter this formula:

=FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-1))

. . . this finds the position of the next to last slash.

2. In column C enter this formula:

=FIND("\",A1,B1+1)

. . . this finds the position of the last slash

3. In column D, the MID - middle - formula pulls the data between the positions identified in the two columns to the left:

=MID(A2,B2+1,(C2-B2)-1)

You can go one folder back further in the file path by changing the formula to get the position of the next slash:

=FIND("|",SUBSTITUTE(A2,"\","|",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))-2))

. . . at the end the 1 is changed to a 2. You can re-use the formula from step 1, and then simply repoint the MID formula to the new range results.

Excel TEXT Function to Display Date in Formula

Don't miss that in Excel when you want to reference a date in another column (that is formatted as a date, not as text) in a formula in another cell and display it in the correct date format, you can use the TEXT function.

In this example we see that when a reference is made to a date in a formula using a simple cell reference, we get the date converted to a number.

If instead the formula is written using the TEXT function:

="Letter dated, "&TEXT(A2,"mm/dd/yyyy")

. . . the date will be shown in the result of formula.

Excel formula to go from A to ZZ

When adding letter suffixes to exhibits, it's common to have to go past Z, and begin a two letter sequence. AA, AB, AC, and so on. This formula will allow you to generate a list of two letter pairs running in order from A to ZZ.

=IF(G9>25,CONCATENATE(CHAR(MOD(QUOTIENT(G9-26,26),26)+65),CHAR(MOD(G9-26,26)+65)),CONCATENATE(CHAR(MOD(G9,26)+65)))

This formula can be placed anywhere on a worksheet, so long as there is a helper column next to it with a list of consecutive numbers from 0 to 701. In this example the formula points to G9 in which I have entered zero. Pulling down the formula with CTRL + D to be adjacent to the list of numbers, will generate a list of letters from A to Z, then from AA, AB, AC . . . to AZ, then from BA to BZ, and from CA all the way to ZZ.

The formula first checks to see if the first 26 letters of the alphabet have already been generated, and then generates the two letter suffixes.

Thanks to Sundar for the idea first posted here.