Formual to Extract month and year from sheet name

[rizmomin]

Hello:

I have a Monthly sheet tab Jan14,Feb14,Mar14, etc.

I need a formula so in
Jan14, I1=1, J1=2014
Feb14, I1=2, J1=2014 and so on.

please Let me know if you have any questions.
Thanks.


Riz

[Ace_XL]

In I1 (array formula)
=MATCH(LEFT(MID(CELL("filename"),(FIND("]",CELL("filename"))+1),255),3),TEXT(DATE(YEAR(TODAY()),ROW(A1:A12),1),"mmm"),0)

In J1
=2000+RIGHT(MID(CELL("filename"),(FIND("]",CELL("filename"))+1),255),2)

[AlKey]

Month
=MONTH(I1)

Year

=YEAR(I1)

	I	J	K
1	14-Jan	1	2014
2	14-Feb	2	2014

[rizmomin]

Hi Ace_XL:

It works, still need to know that you have "filename"..Where is that used.

Works great
Riz

[Ace_XL]

More on the CELL() function. Check these out...

http://office.microsoft.com/en-001/e...010062392.aspx

http://www.techonthenet.com/excel/formulas/cell.php

[rizmomin]

Hi Ace_XL:

Seems to malfundtion.
If i go to another monthly tab, then i see the same month as before.
Please help.
Thanks
Riz

[Ace_XL]

Aah yes of course..Use this instead

Array entered
=MATCH(LEFT(MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),255),3),TEXT(DATE(YEAR(TODAY()),ROW(A1:A12),1),"mmm"),0)
&
=2000+RIGHT(MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),255),2)

Alternatively, you could use a UDF to get sheetname and apply similar formulas. See here
http://www.mrexcel.com/forum/excel-q...into-cell.html

[Tony Valko]

Array entered
=MATCH(LEFT(MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),255),3),TEXT(DATE(YEAR(TODAY()),ROW(A1:A12),1),"mmm"),0)

You can reduce that to:

=MONTH(1&LEFT(MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),32),3))

Normally entered.

[AlKey]

=MONTH(1&REPLACE(CELL("filename",H1),1,FIND("]",CELL("filename",A1)),""))

=YEAR(1&REPLACE(CELL("filename",H1),1,FIND("]",CELL("filename",A1)),""))

[rizmomin]

Hi guys:

I am still not sure where is the Sheet Name in the formula.
I am seeing "filename" in the formula but what is that referring to.


Would appreciate if someone can explain.
Thanks
Riz

[Tony Valko]

The formulas are extracting the sheet name from the file name.

In a file that has been saved and given a name enter this formula EXACTLY as shown in ANY cell:

=CELL("filename",A1)

Filename is the argument and is the information that the CELL function is going to return. "Filename" doesn't mean you're supposed to enter the filename!

The formula returns the full path plus the file name and sheet name.

So, the formula might return something like this:

C:\A Excel Forum\[temp.xlsx]Jan14

Then other parts of the formula extract the sheet name from that string.

[Tony Valko]

You can reduce that to:

=MONTH(1&LEFT(MID(CELL("filename",A1),(FIND("]",CELL("filename",A1))+1),32),3))

Normally entered.

Improvement:

=MONTH(1&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32))

[rizmomin]

Hello:
Now I got it..working great
Thanks
Riz

[Tony Valko]

Good deal. Thanks for the feedback!