Change text name based on file name

[CAM]

On my spreadsheet in cell A1 I have a text label containing the word
"Department" What I want is when I save the file as Schedule 1
(Division).xls I want cell A1 change from "Department" to "Division" based
upon what is within the parentheses in this example Any tips will be
appreciated. Thank you.

 

[Greg Wilson]

If I understand you, you want to return the portion of the wb's name that is
within parentheses. If so, try this formula in cell A1. After doing a "Save
As..." you will have to press F9 to update. Minimal testing:

=MID(CELL("filename", A1), SEARCH("(", CELL("filename", A1), SEARCH("[",
CELL("filename", A1), 1)+1)+1, SEARCH(")", CELL("filename", A1), SEARCH("(",
CELL("Filename", A1), SEARCH("[", CELL("Filename", A1), 1)+1))-SEARCH("(",
CELL("filename", A1), SEARCH("[", CELL("filename", A1), 1)+1)-1)

Regards,
Greg

 

[Bob Phillips]

=MID(CELL("filename", A1), SEARCH("(", CELL("filename", A1),
SEARCH("[",CELL("filename", A1), 1)+1)+1, SEARCH(")", CELL("filename", A1),
SEARCH("(",
CELL("Filename", A1), SEARCH("[", CELL("Filename", A1),
1)+1))-SEARCH("(",CELL("filename", A1), SEARCH("[", CELL("filename", A1),
1)+1)-1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

On my spreadsheet in cell A1 I have a text label containing the word

"Department" What I want is when I save the file as Schedule 1
(Division).xls I want cell A1 change from "Department" to "Division"
based upon what is within the parentheses in this example Any tips will
be appreciated. Thank you.

Unfortunately, both of the other responses will return the wrong information
if one or more directories in the path to your file have parentheses in
them. The following formula will overcome that problem AS LONG AS the
worksheet name itself does NOT have any parentheses in it...

=MID(LEFT(CELL("filename"),FIND("*",SUBSTITUTE(CELL("filename"),")","*",LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),")",""))))-1),1+FIND("*",SUBSTITUTE(CELL("filename"),"(","*",LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"(","")))),264)

Rick

 

[Bob Phillips]

Don't know what happened here as this is just a copy of the other one.

I actually meant

=MID(CELL("filename",A1),FIND("(",CELL("filename",A1),FIND("[",CELL("filename",A1)))+1,
FIND(")",CELL("filename",A1),FIND("[",CELL("filename",A1)))-FIND("(",CELL("filename",A1),FIND("[",CELL("filename",A1)))-1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

=MID(CELL("filename", A1), SEARCH("(", CELL("filename", A1),
SEARCH("[",CELL("filename", A1), 1)+1)+1, SEARCH(")", CELL("filename",
A1), SEARCH("(",
CELL("Filename", A1), SEARCH("[", CELL("Filename", A1),
1)+1))-SEARCH("(",CELL("filename", A1), SEARCH("[", CELL("filename", A1),
1)+1)-1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

On my spreadsheet in cell A1 I have a text label containing the word
"Department" What I want is when I save the file as Schedule 1
(Division).xls I want cell A1 change from "Department" to "Division"
based upon what is within the parentheses in this example Any tips will
be appreciated. Thank you.

 

[Dave Peterson]

The other bad news is that a formula like:

=CELL("filename")

will return the name of the workbook that is active when excel recalcs. If you
open two different workbooks and arrang windows so you can a worksheet from each
workbook and put that =CELL("filename") in a cell in each workbook, you'll see
the problem.

In this case, you'd want to include a reference to the worksheet that contains
the formula. You can use:

=CELL("filename",a1)
or even use the cell that contains the formula.

 

[CAM]

Thanks Bob.

Don't know what happened here as this is just a copy of the other one.

I actually meant

=MID(CELL("filename",A1),FIND("(",CELL("filename",A1),FIND("[",CELL("filename",A1)))+1,
FIND(")",CELL("filename",A1),FIND("[",CELL("filename",A1)))-FIND("(",CELL("filename",A1),FIND("[",CELL("filename",A1)))-1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

=MID(CELL("filename", A1), SEARCH("(", CELL("filename", A1),
SEARCH("[",CELL("filename", A1), 1)+1)+1, SEARCH(")", CELL("filename",
A1), SEARCH("(",
CELL("Filename", A1), SEARCH("[", CELL("Filename", A1),
1)+1))-SEARCH("(",CELL("filename", A1), SEARCH("[", CELL("filename", A1),
1)+1)-1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

On my spreadsheet in cell A1 I have a text label containing the word
"Department" What I want is when I save the file as Schedule 1
(Division).xls I want cell A1 change from "Department" to "Division"
based upon what is within the parentheses in this example Any tips will
be appreciated. Thank you.

 

[CAM]

Thanks Greg

 

[Robert McCurdy]

This works for me.
It doesn't matter what length the files name is when looking for the first [.

=MID(LEFT(CELL("filename"),FIND("]",CELL("filename",A1))-1),FIND("[",CELL("filename"))+1,255)

If someone has put '[ ]' within their directory names then you could find out if there is more than 1 with this..

=SUMPRODUCT(N(MID(CELL("filename"),ROW($1:$256),1)="["))

It would make for one huge formula .... so I'm not doing it


Regards
Robert McCurdy
The other bad news is that a formula like:

=CELL("filename")

will return the name of the workbook that is active when excel recalcs. If you
open two different workbooks and arrang windows so you can a worksheet from each
workbook and put that =CELL("filename") in a cell in each workbook, you'll see
the problem.

In this case, you'd want to include a reference to the worksheet that contains
the formula. You can use:

=CELL("filename",a1)
or even use the cell that contains the formula.

 

[Rick Rothstein \(MVP - VB\)]

Good point Dave!

CAM: Here is my formula modified to account for Dave's comment...

=MID(LEFT(CELL("filename",A1),FIND("*",SUBSTITUTE(CELL("filename",A1),")","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),")",""))))-1),1+FIND("*",SUBSTITUTE(CELL("filename",A1),"(","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"(","")))),264)

Rick

 

[Rick Rothstein \(MVP - VB\)]

This works for me.
It doesn't matter what length the files name is when looking for the first
[.

=MID(LEFT(CELL("filename"),FIND("]",CELL("filename",A1))-1),FIND("[",CELL("filename"))+1,255)

But that doesn't find what the OP asked for. For the sample filename
given...

Schedule 1 (Division).xls

the OP wanted the word Division returned, but your formula will return the
entire filename.

Rick

 

[Greg Wilson]

Both Bob's and my responses ignore parentheses in directories BEFORE the
square brackets containing the wb name. They only look for parentheses within
the square brackets.

Greg

 

[Bob Phillips]

Mine doesn't, I look for ( after the [

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[CAM]

Thanks Rick, Robert, Bob, Greg for your input.

Cheers

Good point Dave!

CAM: Here is my formula modified to account for Dave's comment...

=MID(LEFT(CELL("filename",A1),FIND("*",SUBSTITUTE(CELL("filename",A1),")","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),")",""))))-1),1+FIND("*",SUBSTITUTE(CELL("filename",A1),"(","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"(","")))),264)

Rick

 

[Greg Wilson]

I mispoke. So does mine. Since the OP stated that the wb name contained a
word in parentheses, both formulae should thus find them *within* the square
brackets ("[...]") even though they are only designed to look for it after
the left square bracket ("["). My point was that they both ignore parentheses
in directories in the path to the file is response to Rick's post:

"Unfortunately, both of the other responses will return the wrong information
if one or more directories in the path to your file have parentheses in
them."

 

[Rick Rothstein \(MVP - VB\)]

I mispoke. So does mine. Since the OP stated that the wb name contained a

word in parentheses, both formulae should thus find them *within* the
square
brackets ("[...]") even though they are only designed to look for it after
the left square bracket ("["). My point was that they both ignore
parentheses
in directories in the path to the file is response to Rick's post:

"Unfortunately, both of the other responses will return the wrong
information
if one or more directories in the path to your file have parentheses in
them."

My apologies for that comment... I realize now that when I tested your
routines initially, I must have done so against a String value that didn't
have square-brackets in it.

Rick

 

[CAM]

Thanks Rick. Works great

Cheers

Good point Dave!

CAM: Here is my formula modified to account for Dave's comment...

=MID(LEFT(CELL("filename",A1),FIND("*",SUBSTITUTE(CELL("filename",A1),")","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),")",""))))-1),1+FIND("*",SUBSTITUTE(CELL("filename",A1),"(","*",LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"(","")))),264)

Rick

 

[Rick Rothstein \(MVP - VB\)]

You are welcome. I would like you to consider revisiting Greg and Bob's
formulas again. Based on a comment made today Greg, it occurred to me that I
may have initially tested their codes on a String having no square brackets
in it. I just retested them with the 'right' kind of String and both of
their formulas, in fact, work fine... my initial comment on their posted
formulas was incorrect.

Rick

Thanks Rick. Works great

Cheers