There are times when one wants to extract the last part of a string, say the file name from a string that contains the filename including the path. This short note describes a few ways to do that.

The example we will use is the following. Cell A1 contains the value c:xyz.jpg. What we want is the yz.jpg part.

This note describes three approaches to this task.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0131%20Extract%20last%20token.shtml

Tushar Mehta

Procedure arguments, either for a subroutine or a function, are declared either as ByVal or ByRef. The use of ByVal means that the called procedure does not want to change the value of the argument passed to it whereas the use of ByRef means that the called procedure may change the value.
This note looks at how the called procedure and the calling routine are affected by three different kinds of arguments passed ByVal and passed ByRef.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1004%20ByVal%20ByRef.shtml

Tushar Mehta

The ParamArray argument makes it possible for a procedure (a function or a subroutine) to accept an arbitrary number of arguments, each of a possibly different type. This enables the developer to write a function that will work with one or two or however many arguments that the user of the function passes to it. When used with a User Defined Function (UDF), it works as an Excel function.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1005%20ParamArray.shtml

Tushar Mehta

A binary clock, or more accurately a binary-coded decimal clock, shows each digit in binary. I first saw one at a friend’s place and it served as an interesting ice breaker.

The below image is from Excel. Specifically, it is an Excel chart, though most would be hard pressed to recognize it as such. It represents the time 14:30:26 (i.e., 26 seconds past 2:30 pm).

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0907%20Binary%20Clock.shtml

Tushar Mehta

There have been several requests in various forums to look up data in a two dimensional (2D) table. This documents a couple of the more typical requests.

The first requirement is to find the entry at the intersection of a particular value of the first column and the first row. For example, look up the value for Product KK and Category F.

The second requirement is to find, given the value for a product, the category with the minimum value. The example below looks up the minimum value for Product JJ and then finds the corresponding category, G.

There are variants of this requirement that have the same solution adjusted as required. It could be to find the product that yields the minimum value for a particular category, i.e., go in the opposite direction of the previous example.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0106%202D%20lookup.shtml

Tushar Mehta

The x-axis of a Column chart can contains either category values (the x values are equidistant) or dates. Neither of these allows for a truly numeric x axis. This tip shows how to simulate a column chart that has cardinal numbers on the x axis (cardinal numbers contain a sense of ‘distance’ – e.g., 97 is 2 more than 95, 7 more than 90, and 3 less than 100). Of course, once the axis supports cardinal numbers it also becomes possible to convert it to a logarithm (or log) scale.
Starting with Excel 2007, Microsoft enhanced the charting module to provide greater formatting control over the chart elements. This makes it possible to make a XY Scatter chart look like a column chart!

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0411%20logarithm%20scale%20on%20x%20axis.shtml

Tushar Mehta

Amazon seller lists book at $23,698,655.93 — plus shipping
http://www.cnn.com/2011/TECH/web/04/25/amazon.price.algorithm/index.html?iref=NS1
which references
Amazon’s $23,698,655.93 book about flies
http://www.michaeleisen.org/blog/?p=358

– Tushar Mehta

Starting with Excel 2007, it is possible to specify the number format as part of conditional formatting. This note shows how to leverage that capability to add the appropriate suffix to an ordinal number (e.g., 1st, 2nd, 3rd, 4th, etc.)

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0122%20ordinal%20number%20suffixes.shtml

Tushar Mehta