Here is a way to get the worksheet name into any cell on the worksheet:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

The instance of the CELL function in this formula returns the full name of the worksheet, including the filename and file path. It would normally return something like this:
C:\Documents and Settings\ExcelAssist\Documents\[test.xls]Sheet1

Use the FIND function to strip out everything except the tab name.

How To Extract text from cells

There may be times when you need to extract certain characters from text within a cell. For example, a general ledger code such as S-20-1010 may be comprised of a character representing the region, a 3-character subcode representing the department and a 4-character subcode representing the expense type. If you are required to extract each of these subcodes into separate columns, using Excel’s string functions can save you a whole lot of time.

The LEFT function returns a desired number of characters from the beginning of a text string. The RIGHT function returns a desired number of characters from the end of a text string. The MID function returns a desired number of characters starting at a specified position within the text string.

Assuming your general ledger codes are in column B and you want to extract the subcodes to columns C, D and E.
Excel Text functions sample

LEFT Function: In our example, to extract the region code, which is the left-most character, you can use the LEFT function.

=LEFT(text_string, #_of_characters)

In cell C2 you would enter the formula =LEFT(B2,1) and this would return the region code W. Here the region code is just one character. In other cases you can extract any number of characters by changing the #_of_characters attribute in the formula.

RIGHT Function: To extract the expense code, which is comprised of the 4 right-most characters, you can use Excel’s RIGHT function.

=RIGHT(text_string, #_of_characters)

In cell D2 you would enter the formula =MID(B2,4) and this would return the expense code 5095.

MID Function: To extract the department code, which is in the middle of this text string, you can use Excel’s MID function

=MID(text_string, start_pos, #_of_characters)

The difference with the MID function is that you specify at which character position in the text string you start. In cell E2 you would enter the formula =MID(B2,3,3) and this would return the expense code 320.