The following works if the month name is the same for the entire sheet.
A1 =CELL("Filename")
A2 =FIND(".";A1)
A3 =FIND("January";A1)
A4 =MID(A1;A3;A2-A3)
A5 =FIND("/";A4)
A6 =RIGHT(A4; LEN(A4)-A5)
Change A3 to point to a cell where the month is located rather than hard
coding it. You can probably combine the six formulas into one long formula.
--
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]
> Dennis,
>
> Thank you once again.
>
> I want only the file name portion of that string. Let's take a
> hypothetical
> case in which I am processing data for a company (Itty Bitty Marketers)
> which
> has a payroll department which organizes information into salaried or
> hourly
> and for hourly it has virtual pay stubs and time sheets with the time
> sheets
> organized by month. There is a separate file for each employee (e.g. Fred
> Jones) and there are five sheets named Week 1 through Week 5. It is
> desired to
> display the employee name as taken from the file name. The
> cell("filename")
> function returns something like this:
>
> 'file:///home/jelang/My%20Customers/Itty%20Bitty%20Marketers/Payroll%20Departmen
> t/Hourly/Time%20Sheets/January%202005/Fred%20Jones.ods'#Week 3
>
> In this case what I want to extract and display are the ten characters
> "Fred
> Jones" so I need to locate and remove everything through some eleven
> slashes as
> well as everything from the dot to the end of the string. Similar files
> for
> other companies for which I process data may have their files organized in
> directory trees of differing complexity. I want the file name extraction
> to
> work seamlessly. The constants are that I want to display everything
> between
> the final slash and the first dot. If only I could search right to left
> for the
> final slash it would be easy but that does not appear to be a feature of
> OOo's
> Find() function. I attempted to specify a negative number for start
> position on
> the chance that OOo would use that as a cue to perform a right to left
> rather
> than left to right scan but it did not work. As long as there are not more
> than
> 32 slashes (an awful thought) defining the directory structure my
> implementation, though ugly, will do the trick. Actually my method removes
> the
> part through the first three slashes in one operation along with the
> trailing
> sheet name portion.
>
> As you can probably deduce from the string above, I am running OOo on a
> Linux
> platform.
>
> --
> Jim
>
> Dennis Marks wrote at 17:17 on 8 May 2006:
>
>> Since there is only a single dot (.) in the name doesn't my method work
>> no
>> matter how many levels of directories. It will return the final 8
>> characters
>> prior to the dot. It doesn't matter how many directories (slashes). Now
>> it's
>> only a matter of removing any slashes for shorter names. It would
>> probably
>> be no more than one slash which can be found using FIND. With this
>> position
>> you can keep only the RIGHT portion. This has not been fully tested and
>> maybe your naming convention will not work using it.
>>
>> --
>> Dennis
>>
>> Disclaimer: The above is my opinion. I do not guarantee it. ...
>>
>> "James E. Lang" <[EMAIL PROTECTED]> wrote in message
>> news:[EMAIL PROTECTED]
>> > 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. ...
>> >>
>> >> "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]
