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))

Importing Autocomplete File into Outlook 2010

This is something I answered over at Experts Exchange and thought I'd post here as well.

The .NK2 file used by Outlook 2003 and Outlook 2007 and is used to suggest addresses when you start typing in the recipients field is no longer used by Outlook 2010.

This file can be imported by Outlook 2010 and the contacts placed in the "Suggested Contacts" folder in the mailbox.

Copy the .NK2 file to the "C:\Users\%username%\AppData\Roaming\Microsoft\Outlook" folder (assuming the client is Windows 7)

Rename the nk2-file to the name of your mail profile:

     In the Control Panel, type "mail" into the search box.
     Run the Mail applet.
     Click on the Show Profiles… button.
     By default, your profile is called “Outlook”. So in that case you would call your file “outlook.nk2”.

Start Outlook with the /importnk2 switch:

     outlook.exe /importnk2

Outlook will import the NK2 data into the Suggested Contacts folder.

Office Autosave Locations

I always thought that the autosave for an Office file was created in the same location as the file. It turns out that this was because I almost always work with Office files on network drives.

When a new file is started a temporary file is created. This can be either in the windows temp directory, in "C:\ Documents and Settings\<username>\Application Data\Microsoft". If the file is stored on a network drive then it will be temporarily created there.

This temporary file will have a few different letters after the tilde (or squiggly line “ ~”) . These are good ones to look for to find some lost info. There are others, but these are the ones most likely to contain data that can be recovered.

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"