hello,

I have a problem with a formula
my formula begins with
=MID(B102,FIND("*",SUBSTITUTE(B102,"\","*",LEN(B10 2)-LEN(SUBSTITUTE(B102,"\",""))))+1,LEN(B102))

This formula extracts from cell b102 a filename e.g.
c:\windows\desktop\newfile.xls (b102)
In the cell with the formula the value is then newfile.xls

Now i want to extract from b102 (c:\windows\desktop\new.xls)
c:\windows
Does anybody know how this can made possible?

Regards,

Niek



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

one way:

=LEFT(B102, FIND("\",B102,4)-1)

In article ,
solo_razor wrote:

hello,

I have a problem with a formula
my formula begins with
=MID(B102,FIND("*",SUBSTITUTE(B102,"\","*",LEN(B10 2)-LEN(SUBSTITUTE(B102,"\","
"))))+1,LEN(B102))

This formula extracts from cell b102 a filename e.g.
c:\windows\desktop\newfile.xls (b102)
In the cell with the formula the value is then newfile.xls

Now i want to extract from b102 (c:\windows\desktop\new.xls)
c:\windows
Does anybody know how this can made possible?

hello,

The formula is incomplete, i must extract different filename's
E.G.
c:\program files\eag\dir\testing\test.xls

Now the formula must extract c:\program files\eag\dir\

another example:
c:\windows\system\systemroot\test\testing.xls

The formula must extract c:\windows\system\systemroot\

So you can see, the formula must extract the file and its folder, so 2
steps

I can't get it to work, hope you can

Regards,
Niek



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

=Left(B102,len(B102)-len(cell with your formula that extracted the
filename))


"solo_razor" wrote in message
...
hello,

The formula is incomplete, i must extract different filename's
E.G.
c:\program files\eag\dir\testing\test.xls

Now the formula must extract c:\program files\eag\dir\

another example:
c:\windows\system\systemroot\test\testing.xls

The formula must extract c:\windows\system\systemroot\

So you can see, the formula must extract the file and its folder, so 2
steps

I can't get it to work, hope you can

Regards,
Niek



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Try:

=MID(B102,FIND(CHAR(22),SUBSTITUTE(B102,"\",CHAR(2 2),LEN(B102)-LEN(SUBSTITUTE(B102,"\",""))-1))+1,999)

or possibly:

=MID(B102,1,FIND(CHAR(22),SUBSTITUTE(B102,"\",CHAR (22),LEN(B102)-LEN(SUBSTITUTE(B102,"\",""))-1)))

solo_razor wrote in message ...
hello,

The formula is incomplete, i must extract different filename's
E.G.
c:\program files\eag\dir\testing\test.xls

Now the formula must extract c:\program files\eag\dir\

another example:
c:\windows\system\systemroot\test\testing.xls

The formula must extract c:\windows\system\systemroot\

So you can see, the formula must extract the file and its folder, so 2
steps

I can't get it to work, hope you can

Regards,
Niek



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/