August 28, 2004
Extracting a filename from a full path (Windows)
This would be an easy task if we were looking for the first backslash in the full pathname.
We need to first determine the number of backslashes by figuring out the lenght of the original path with the backslashes removed ...
LEN(A1)-LEN(SUBSTITUTE(A1;"\";"")
We now swap out the last backslah with a space character - CHAR(13) ..
SUBSTITUTE(A1;"\";CHAR(13);LEN(A1)-LEN(SUBSTITUTE(A1;"\";"")
Finally, we use the MID function complete the extraction of the filename.
=MID(A1;FIND(CHAR(13);SUBSTITUTE(A1;"\";CHAR(13);LEN(A1)-LEN(SUBSTITUTE(A1;"\";"")
Posted by Dave at August 28, 2004 05:57 AM
Comments
David,
First of all, HI!!, and Happy New Year 8-)
Just found your site after a search on Google looking for some tips. If you peruse OOo Forums, you will see my various help requests in the CALC section.
As a summary, I am attempting to use the LOOKUP function with "EXTERNAL SCALC FILES". I know the function does exactly what I want it to do if I import a file into sheet2. However, the qty of files to open is >1900, so this is impractcal one at a time, and cannot do them all at the same time.
Anyway, your excellent tutorial on FUNCTIONS seems a good place to ask this, as all of these FUNCTIONS appear to use similar constructs.
2 issues:
1.) How to embed a call to a specific sheet in a specific 'external' .SXC file within the LOOKUP() construct?
2.) How to use a variable as the name of the called file?
I think I can do 2.) once 1 is working, but 1 is proving the hangup. If you check my posts on OOo, you will see the various things I have tried.
The construct is: =LOOKUP(valueref; fname+sheet1+A1:fname+sheet1+D237)
Testing this basic function within the same calc doc works great and returns the value from the correct cell in column D. Beautiful!, but just cannot make it call a file externally.
I know a macro could do it, but that is a very steep learning curve for me at this point, though I am learning.
I CAN get external data using INDIRECT(CONCATENATE.......), but that is cumbersome, and would be more so in this case, since I would also have to use INDEX and MATCH. LOOKUP does it all.
It seems to come down to how the local URL is configured, and whether it is in single or double quotes and/or front or back slashes...
I have tried every combination I an think of, either get Error 504 or 508 or 525.
Well, I have rambled on enough.
I hope the New Year is a good one for you and your family.
Peace - Dom
PS Sorry for the formatting...need to learn html too!
Posted by: Dom at December 31, 2004 01:40 PM
Thanks so much for taking the time to post these tips Dave. You saved me a heap of time as I don't use spreadsheets often, but once or twice a year I have some very large ones I need to mess with.
The way you show the examples is excellent. Just great. Thanks!!
Regards, Paul Evans
Posted by: Paul Evans at February 18, 2005 08:53 PM
Just wonderfull : I was looking for a long time to get simple macros ... to start with...
Many are too complicated to remember...
yours are perfect for a start :)
Thanks a lot
king regards
Henri
Posted by: Henri Girard at March 3, 2005 10:20 PM