Change text name based on file name

  • Thread starter Thread starter CAM
  • Start date Start date
C

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.
 
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
 
=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.
Click to expand...

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
 
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)

Bob Phillips said:
=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)

CAM said:
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.
Click to expand...
Click to expand...
 
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.
 
Thanks Bob.

Bob Phillips said:
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)

Bob Phillips said:
=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)

CAM said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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
 
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
 
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
 
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)
 
Thanks Rick, Robert, Bob, Greg for your input.

Cheers

Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
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."
 
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."
Click to expand...

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
 
Thanks Rick. Works great

Cheers


Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
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


CAM said:
Thanks Rick. Works great

Cheers
Click to expand...
 
Back
Top