Using Part of the Tab Name in a Cell

I have a tab named 1403-JE-PG-1
I want to place the value of everything to the right of the third - of the Tab in a cell OR, IN THE ALTERNATIVE
I want to place the value of the last character of the Tab in a cell.
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
You can simply use this...

=RIGHT(CELL("filename"),1)

Open in new window


This will give you only the last char of your sheet name...

Saurabh...
0
 
ProfessorJimJamCommented:
put this formula in any cell.

=LEFT(IF(ISERROR(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))),"This workbook never saved",RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))),1)

Open in new window

0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
Your formula is giving me the first character in the Tab, not the last character.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ProfessorJimJamCommented:
Replace my formula begging "left" with "right"
0
 
ProfessorJimJamCommented:
Use this

=RIGHT(IF(ISERROR(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))),"This workbook never saved",RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))),1)
0
 
Bill GoldenExecutive Managing MemberAuthor Commented:
The simplest solution would seem to be the best solution.  

I was unsure of what to do when the PG-1 (which indicates the page number) grew to PG-10.  Eureka, I changed the last 1) in either formula to a 2) so -1 become 1 and -10 becomes 10.

Thanks.

BSB
0
 
Rodney EndrigaData AnalystCommented:
This should provide you with the value after the 3RD Hyphen in your Sheetname:
=RIGHT(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1)),LEN(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1)))-FIND("‡",SUBSTITUTE(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1)),"-","‡",3)))

Open in new window


Whether the digit is 1, 2, 3, or so on, it will provide the following:
1403-JE-PG-1            result in cell-->  1
1403-JE-PG-123       result in cell-->  123
1403-JE-PG-1234     result in cell-->  1234
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.