File Path manipulation in Excel

Saw this over at stackoverflow. Had to make a note of it for future reference.

http://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string

Let's say for example you want the right-most \ in the following string (which is stored in cell A1):

Drive:\Folder\SubFolder\Filename.ext

To get the position of the last \, you would use this formula:

=FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))

That tells us the right-most \ is at character 24. It does this by looking for "@" and substituting the very last "\" with an "@". It determines the last one by using

(len(string)-len(substitute(string, substring, "")))\len(substring)

In this scenario, the substring is simply "\" which has a length of 1, so you could leave off the division at the end and just use:

=FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))

Now we can use that to get the folder path:

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

Here's the folder path without the trailing \

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

And to get just the filename:

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

However, here is an alternate version of getting everything to the right of the last instance of a specific character. So using our same example, this would also return the file name:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

Excel: Splitting Names

I've been working on a project that requires the manipulation of name data. A lot of the names were given as full names, some with multiple last names, that for the purposes of data manipulation had to be split. Here's the simple way to do it:

Assuming data is in A1 and the value of the cell is "Charles Kingsford Smith"

For First Name use:

=LEFT(A1,FIND(" ",A:A)-1)

for a resulting value of "Charles"

and for Last Name use:

=MID(A1,FIND(" ",A1)+1,LEN(A1))

for a resulting value of "Kingsford Smith"

Excel: Listing Distinct Elements in a List

I found this info over at http://www.cpearson.com which is an absolutely awesome Excel reference. I wanted to copy these bits here so that I wouldn't lose them.

You can use a simple formula to extract the distinct elements in a list. Suppose your list begins in cell C11. In some cell, enter

=IF(COUNTIF($C$11:C11,C11)=1,C11,"")

Eliminating Blank Cells from a list

You can use a formula to return only the non-blank cells from a range. The following function will return all the cell values in a range named BlanksRange that are not empty.

Create a range name, with the same number of rows as BlanksRange called NoBlanksRange. The range NoBlanksRange must have the same number of rows as BlanksRange but it need not be in the same row numbers. Enter the following Array Formula in the first cell of NoBlanksRange, and then use Fill Down to fill out the range:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

The first N rows of NoBlanksRange will contain the N non-blank cells of BlanksRange. Note that this is an array formula, so you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }.

Note that if you do not use named ranges and enter the actual cell references, you must use absolute cell references (e.g., $B$1) rather than relative cell references (e.g., B1).