Here's what I'm looking for....
Say the file name is: "Eval - Technical - Offeror01.xls" and there will be
several different spreadsheets "...Offeror02", "...Offeror03", etc.

I would like to extract from the file name the Offeror01 part. I've used
this equation befo
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

But now (as I said) want to select out that last part.

Any ideas?

Thanks,
MJohn

Always 9 characters offeror##???

if yes:

=MID(CELL("filename",A1),SEARCH("offeror",CELL("fi lename",A1)),9)



M John wrote:

Here's what I'm looking for....
Say the file name is: "Eval - Technical - Offeror01.xls" and there will be
several different spreadsheets "...Offeror02", "...Offeror03", etc.

I would like to extract from the file name the Offeror01 part. I've used
this equation befo
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

But now (as I said) want to select out that last part.

Any ideas?

Thanks,
MJohn

--

Dave Peterson

Hmm, well, that was easy.
Thanks. Most appreciated.

MJohn

"Dave Peterson" wrote:

Always 9 characters offeror##???

if yes:

=MID(CELL("filename",A1),SEARCH("offeror",CELL("fi lename",A1)),9)



M John wrote:

Here's what I'm looking for....
Say the file name is: "Eval - Technical - Offeror01.xls" and there will be
several different spreadsheets "...Offeror02", "...Offeror03", etc.

I would like to extract from the file name the Offeror01 part. I've used
this equation befo
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

But now (as I said) want to select out that last part.

Any ideas?

Thanks,
MJohn

--

Dave Peterson

If the filenames aren't always in that form (offeror##), is there a way to
search for and extract the part between the 2nd space and the "." in the
filename extension?

Thanks,
MJohn

"Dave Peterson" wrote:

Always 9 characters offeror##???

if yes:

=MID(CELL("filename",A1),SEARCH("offeror",CELL("fi lename",A1)),9)



M John wrote:

Here's what I'm looking for....
Say the file name is: "Eval - Technical - Offeror01.xls" and there will be
several different spreadsheets "...Offeror02", "...Offeror03", etc.

I would like to extract from the file name the Offeror01 part. I've used
this equation befo
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

But now (as I said) want to select out that last part.

Any ideas?

Thanks,
MJohn

--

Dave Peterson

I'd use a couple of cells to make the formulas manageable.

Use your previous formula to extract the workbook name.
(I put it in A1)

Then you can use this formula to get the position of the second space:
=FIND(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),2))
(add one to find the character after the second space:
=FIND(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),2))+1
(I put it in B1)

Then (I bet) each file will end with .xls, so you could just drop that portion:
=MID(A1,B1,LEN(A1)-B1-4+1)
(in C1)

But with a name like: "Eval - Technical - Offeror01.xls"
wouldn't you want the portion after the 4th space character?)
=FIND(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),4))+1



M John wrote:

If the filenames aren't always in that form (offeror##), is there a way to
search for and extract the part between the 2nd space and the "." in the
filename extension?

Thanks,
MJohn

"Dave Peterson" wrote:

Always 9 characters offeror##???

if yes:

=MID(CELL("filename",A1),SEARCH("offeror",CELL("fi lename",A1)),9)



M John wrote:

Here's what I'm looking for....
Say the file name is: "Eval - Technical - Offeror01.xls" and there will be
several different spreadsheets "...Offeror02", "...Offeror03", etc.

I would like to extract from the file name the Offeror01 part. I've used
this equation befo
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

But now (as I said) want to select out that last part.

Any ideas?

Thanks,
MJohn

--

Dave Peterson


--

Dave Peterson

Okay. Wow. Thanks. Excellent work.

MJohn

"Dave Peterson" wrote:

I'd use a couple of cells to make the formulas manageable.

Use your previous formula to extract the workbook name.
(I put it in A1)

Then you can use this formula to get the position of the second space:
=FIND(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),2))
(add one to find the character after the second space:
=FIND(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),2))+1
(I put it in B1)

Then (I bet) each file will end with .xls, so you could just drop that portion:
=MID(A1,B1,LEN(A1)-B1-4+1)
(in C1)

But with a name like: "Eval - Technical - Offeror01.xls"
wouldn't you want the portion after the 4th space character?)
=FIND(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),4))+1



M John wrote:

If the filenames aren't always in that form (offeror##), is there a way to
search for and extract the part between the 2nd space and the "." in the
filename extension?

Thanks,
MJohn

"Dave Peterson" wrote:

Always 9 characters offeror##???

if yes:

=MID(CELL("filename",A1),SEARCH("offeror",CELL("fi lename",A1)),9)



M John wrote:

Here's what I'm looking for....
Say the file name is: "Eval - Technical - Offeror01.xls" and there will be
several different spreadsheets "...Offeror02", "...Offeror03", etc.

I would like to extract from the file name the Offeror01 part. I've used
this equation befo
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

But now (as I said) want to select out that last part.

Any ideas?

Thanks,
MJohn

--

Dave Peterson


--

Dave Peterson