import file name

[bigdan]

I wanted to know how to import the current file's name into an excel cell. also how to shorten it to just the relevant part.

here's the formula i found:


<CODE>=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)</CODE></PRE></PRE>

can someone explain it to me? i dont get it. is there also some place, maybe within excel, where i could've gotten an explanation? when i click on the formula there's a small popup that shows where one is in it, but not an actual explanation.

now if i want to restrict this answer to, say, 10 letters, how would i do so?

 

[John Davis]

I wanted to know how to import the current file's name into an excel cell. also how to shorten it to just the relevant part.

here's the formula i found:


<CODE>=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)</CODE></PRE></PRE>

can someone explain it to me? i dont get it. is there also some place, maybe within excel, where i could've gotten an explanation? when i click on the formula there's a small popup that shows where one is in it, but not an actual explanation.

now if i want to restrict this answer to, say, 10 letters, how would i do so?

In any cell in the workbook. =CELL("filename",A1) will give you the filename. In the Help file lookup the CELL Worksheet Function

 

[bigdan]

basically i want the final result to be the name of the file, in two parts

a typical file name is: CM-M0512-070 Reverse BAC0420 - Payroll

I want part of the code to appear in one cell (the CR-M0512-070 part, which is always the same length) and the rest of it to appear in a second cell; however the second cell is not always the same length.

the first part i've been able to do, i just take the above command (my first post's command) and attach a (left, 12) to it.
the second part of it, how do i do that?

btw this formula of yours
=CELL("filename",A2)

why did you put an A1 in there, what's the point? with my experimentation its not making a diff whether i put A1 or A5 or remove it altogether. i guess its doing smth else im not seeing?

 

[Trouttrap2]

If you would to see how the formulas work, you can use the formula evaluator. Click ALT>T>U>F. Yep, this is a huge and hard to read understand formula.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Mofifying your formula to get the first part:<o></o>
=LEFT(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1),27)<o></o>
<o> </o>
To get the second part, use this:<o></o>
=RIGHT(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1),LEN(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1))-LEN(LEFT(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1),27))-2)<o></o>

 

[Trouttrap2]

Sorry, didn't thoroughly read your post of what you wanted. Disregard my earlier post.

To get the second part, since you already have the first, use this:
=TRIM(RIGHT(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1),LEN(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1))-LEN(A5)))

Where A5 holds the first part that you already have.

 

[Ingolf]

Hi,

IF your formula is in A1 put in B1 =LEFT(A1;12) and

in C1 =RIGHT(A1;LEN(A1)-LEN(B1)-1)