15 Jan

How to Get Name of Worksheet, File, Folder and Path with Excel formula

There are times when we need to get the name of the Excel file, the path or the worksheet that we are working on. The following formula shows you how to do so.

Firstly, to get the full path, file name and current worksheet name in a cell, the formula is
=CELL(“Filename”)

The rest of the formulas needed are shown below. For each of the formula, you can replace “B$2” with the formula in B$2 accordingly if you need the formula in a single cell.

The actual results of the formula in Column B are shown below.

However, in the unlikely event that any of your folders in the path or the file name contains square brackets “[” or “]”, you would need a set of different formulas. (It’s a pain, so try not to have any of your folders or file names to have square brackets!)

The actual results in Column B are shown below (it’s the same as the one above).

This latter set of formulas makes use of the formula for finding the last occurrence of a character in a string.

In summary, the formulas to find the following (assuming no square brackets in your folder path or file name) are:

(1) Folder path
=LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)

(2) File name with path
=SUBSTITUTE(LEFT(CELL(“Filename”),FIND(“]”,CELL(“Filename”))-1),”[“,””)

(3) File name without path
=MID(CELL(“Filename”),FIND(“[“,CELL(“Filename”))+1,FIND(“]”,CELL(“Filename”))-FIND(“[“,CELL(“Filename”))-1)

(4) Sheet name
=RIGHT(CELL(“Filename”),LEN(CELL(“Filename”))-FIND(“]”,CELL(“Filename”)))

Let me know if you need any explanation of the above. Thanks!

31 Dec

How To Find Last Occurrence of Character in a String

There may be some instances where you will need to find the last occurrence of a specific character in a string in Excel. The following example shows you how.

Assuming that your original string is in cell B2 and the character that you want to find is “.” , the formula to give you the position of the last occurrence of the character in the string is:
=LEN(B2)-LEN(TRIM(RIGHT(SUBSTITUTE(B2,”.”,REPT(” “,LEN(B2))),LEN(B2))))

The following shows a breakdown of the above formula:

The key to this example is this magical formula, which gives you everything to the right of the last occurrence of the character “.”.
=TRIM(RIGHT(SUBSTITUTE(B2,”.”,REPT(” “,LEN(B2))),LEN(B2)))

Let me know if you need more explanation of the above. Thanks!

15 Dec

How To Shorten Number Formats with Millions or Thousands

When we have large numbers, we often want to use the contraction “K” or “M” for thousands or millions respectively. To do this in Excel, we need to change the number format to the following.

For thousands (and 2 decimal places):
0.00,”K”
[1,234 is shown as 1.23K]

For millions (and 2 decimal places):
0.00,,”M”
[1,234,567 is shown as 1.23M]

One good use of number formats is in charts, as seen below. This uses the format 0,,”M” in the vertical axis and the format 0.00,,”M” in the data labels.

The above is based on a very good reference on number formats from Peltier Tech (http://peltiertech.com/Excel/NumberFormats.html) which has detailed explanations and as many things as you can think of to do with number formats!

The site also provides a conditional number format – where numbers greater than 1 million are shown with an “M”, and numbers greater than 1 thousand are shown with a “K”.

[>=1000000]0,,”M”;[>=1000]0,”K”;0