filename search and extract into a cell

G

Guest

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 before:
=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
 
D

Dave Peterson

Always 9 characters offeror##???

if yes:

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



M said:
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 before:
=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
Click to expand...
 
G

Guest

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

MJohn

Dave Peterson said:
Always 9 characters offeror##???

if yes:

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



M said:
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 before:
=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
Click to expand...
Click to expand...
 
G

Guest

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 said:
Always 9 characters offeror##???

if yes:

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



M said:
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 before:
=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
Click to expand...
Click to expand...
 
D

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 said:
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 said:
Always 9 characters offeror##???

if yes:

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



M said:
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 before:
=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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Okay. Wow. Thanks. Excellent work.

MJohn

Dave Peterson said:
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 said:
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 said:
Always 9 characters offeror##???

if yes:

=MID(CELL("filename",A1),SEARCH("offeror",CELL("filename",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 before:
=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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting sheet name and info from cell 3
get value of cell only 6
Excel [VBA] Check from a cell into another sheet and bring back result in MsgBox 0
inputting filename into a cel 1
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION 1
Function to put worksheet name in cell returns text, need number 5
Cell value as filename 1
Can I use a worsheet name (filename), and a range name as values? 2

Top