Find Last Character in Excel

From time to time in Excel, I find that I need to pull off a sub-string before or after the last instance of a character in some text.

Examples:

1. I have a list of fully-qualified filenames. I want to split the name putting the full path in one cell and just the filename in another cell.
2. I have a list of corporation names followed some number. I want to put the name (which could be made up of any number of words) in one cell and the number in another.

In order to do this, I need to find the last position that a specific character occurs in the source text. For the filenames, I need to find the last "\" or "/" character. For the corporate names, I need to find the last space character.

Excel does not have a SPLIT or FINDLAST formula. It does, however, have a SUBSTITUTE method that proves to be quite helpful.

With the original text in cell J2, here's the answer to get the text before the last space:

=LEFT(J2, FIND("*", SUBSTITUTE(J2, " ", "*", LEN(J2) - LEN(SUBSTITUTE(J2, " ", "")))) - 1)

And, here's the answer to get the text after the last space:

=RIGHT(J2, LEN(J2) - FIND("*", SUBSTITUTE(J2, " ", "*", LEN(J2) - LEN(SUBSTITUTE(J2, " ", "")))))

TFS Work Items

Here are a few things I've discovered while trying to work with Work Items in TFS. We are currently using the MS Agile Work Items without any modifications.

Go to Work Item...
There is a very useful menu option, Team | Go to Work Item..., which brings up a dialog box where you can enter a Work Item ID. Click OK and the specified Work Item is opened.

History Notes
On the History tab, there is an area which has a the watermark text "Type your comment here." By clicking there and entering any text and then saving the work item, your text will appear in the list of history items below the date/time and above the changed fields.

This is a useful location to type a message for the person to whom you are (re)assigning a Task, Bug or Scenario. This location is also a useful location to record your thoughts, comments or opinions about a given work item.

Before I discovered this feature, I had been adding comments to the Description tab. I'm glad to know about this comments area in the History tab as this seems like a much more appropriate place for comments.

Add Related Work Item
This menu option allows you to create a new Work Item which will be linked to the current Work Item.

I've discovered a couple of ways to get to the Add Related Work Item menu option.

From the main menu, click Team | Add Related Work Item, and then choose Bug, Task, Scenario, etc.

Or, from within any Work Item, right click anywhere in the main body of the Work Item above the tabs. This will bring up the context menu which has the Add Related Work Item option. You can then choose Bug, Task, Scenario, etc.

Add Link
Of course, you might need to link to an existing Work Item rather than creating a new one.

To do this, select the Links tab and click the Add... button. This brings up the Add Link dialog.

On this dialog, the Link Type should have the Work Item selected. You can also link to a Changeset, Versioned Item, Test Result or Hyperlink. Just leave the Link Type as a Work Item.

If you know the ID of the desired Work Item, just enter that ID in the Work item ID field. If not, click the Browse... button. Which brings up the Choose Related Work Item dialog. I like this dialog because of all the ways that you can use to find the related Work Item. I usually use the Title contains option and might even select a specific type for the and type drop down list.

Once the Work item ID has been entered or selected into the Add Link dialog, the Description field will show the title of the Work Item. (If not -- e.g., because you just entered the number -- press the tab key, and it should appear.)

You can enter something in the Comment field if necessary. I usually leave this blank except when one Scenario uses another one -- then I put something like "688 uses 681."

Once you've got the right Work Item, press the enter key or click the OK button.

Using TFS

I've been using TFS for about a year now. I have to admit that learning to use TFS has not been easy. Most of the team development environments I've used over the years have been home grown, and while I was not the one that created them, I found them all quite easy to use. I've even used some of the open development sites--like SourceForge--and have found these fairly easy to use. TFS has been a bit more difficult.

My experience with TFS is similar to most of the stuff that I encounter from MS -- it is highly configurable and not intuitive.

My current development team had moved from SourceSafe to TFS just a few months before I joined the group and they were not using most of the features of TFS at that time. When I arrived, we had a single TFS project with all of the VS projects checked-in. The build system was external to TFS. There were no solution files checked in, so each developer create a separate solution file on their local systems in order to build. There were several web service projects and many more DLL projects. There were also plenty of harness applications to exercise the web services and/or DLLs. There were also a handful of VS plug-ins and/or utility programs -- most of which were not under revision control.

During the past year, I have worked to get the TFS Build working which required the addition of solution files -- a good thing. Once I got the first TFS Builds running, others on the team set up the automated builds and created other TFS Build files. I have also worked at getting automated unit tests running. We had several discussions about whether to have a single unit test DLL for an entire solution or a separate unit test DLL for each DLL created within a solution. Again, once I got the first unit tests running, others on the team have taken to creating unit tests. I have to admit that someone else brought in the idea of mock objects, and I personally have not been sold on them yet. We also now have several separate TFS projects for each of the web services. This has been a bit confusing for some of the developers as they were used to working out of just a single repository project. I am now working on getting the use of Work Items to be a part of the everyday lives of the developers.

(As it's time for food and other things in my life, I'll have to blog about the details of those items later.)

Getting Started

I've been intending to get started at this blogging stuff -- but I keep on just working. Actually, I keep thinking of what cool stuff I'm going to put here on my blog -- but I keep on just working. I've even thought about going back through all the projects that I've done in the past and post things that I learned way back when -- but I keep on just working.

Well, enough is enough. Now, I have another blog post.

First Blog

Hmm.... Am I blogging now?