Thank you Dennis for your very prompt response. Since it appears that it is
impossible to perform a "find" from the right end of the string back toward the
left end I have had to take your advice to "Maybe do the second parse in
another cell" but since the path name that I am attempting to strip contains a
number of directory names I have had to do this multiple times. In my case, I
am using this information in a merged cell (B39:AK39) which hides 35 other
cells so I have placed this formula in cell C39:
SUBSTITUTE(CELL("filename");"%20";" ")
It takes the returned value of cell("filename") and changes all the %20 values
to spaces and stores the result in cell C39. Then I have placed this formula in
cell D39:
MID(C39;10;FIND("'";C39;2)-10)
It strips the leading single quote and "file:///" from the start of the string
and the trailing single quote plus the sheet name from the end of it and stores
the result in D39. Then I have placed this formula in cell E39 and filled it
right through cell AJ39:
IF(FIND("/";D39&"/")<LEN(D39);RIGHT(D39;LEN(D39)-FIND("/";D39));D39)
Each of these copies removes everything through the first slash starting at the
left end of the string that it finds in the prior cell. By filling the cells
right with this formula I am able to strip up to 32 slashes which ought to be
more than sufficient. I use the following formula in cell AK39 to finish the
parsing:
IF(FIND(".";AJ39&".")<LEN(AJ39);LEFT(AJ39;FIND(".";AJ39)-1);AJ39)
This strips all file extensions that may exist. Finally I reference cell AK39
in my displayed text with another formula like this:
"verbiage "&AK39&" more verbiage"
Cells C39 through AK39 are all hidden behind the merged cell so it looks
cleaner than it appears in this description. Though not clean, this does work.
I do wish that there were a cleaner way to do this.
--
Jim
Dennis Marks wrote at 7:57 on 6 May 2006:
> The following will work if the filenames are a fixed length. I am assuming 8
> characters.
> =MID(CELL("filename");FIND(".";CELL("filename"))-8;8)
>
> If the length is variable then you could include another find() for the
> slash and a right() to remove it and preceding characters but this will make
> it much more complicated. Maybe do the second parse in another cell.
>
> --
> Dennis
>
> Disclaimer: The above is my opinion. I do not guarantee it. Be sure to back
> up any files involved and use at your own risk. Batteries not included. Not
> for internal use. Don't run with knives.
>
> "James E. Lang" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > I want to display the filename within a CALC document. I do not want to
> > include the path or the filename extension.
> >
> > I have only been able to find one way to obtain the name of the CALC
> > document file using standard functions. That is 'cell("filename")' but that
> > gives me a whole lot of extra text that I don't need or want.
> >
> > Is there a cleaner way? If not, then how can I parse the text generated by
> > this function to strip the path and everything starting with the filename
> > extension? I see no way to search a text string from right to left for a
> > sub-string. I find it hard to believe that this is an extremely rare
> > requirement.
> >
> > I do not want to write a function in Basic to accomplish this task since I
> > would like this to be compatible with Excel.
> >
> > I am using OOo version 2.0 (Build 2.0.0.1). I downloaded version 2.0.2
> > yesterday and will be upgrading to it extremely soon.
> >
> > --
> > Jim
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
