how to extract a filename?
 

a cell contains:

C:\Program Files\Amersham Biosciences\ImageMaster 2D Platinum
Trial\Gels\94-0005.mel


How to obtain 94-0005 in this case? certainly, the path can also be d: and
the filename is of any combinations of characters in general.

i find "FIND, MID, MATCH, RIGHT, ..." doesn't do a good job, thx!!

C:\Program Files\Amersham Biosciences\ImageMaster 2D Platinum
Trial\Gels\94-0005.mel


How to obtain 94-0005 in this case? certainly, the path can also be d: and
the filename is of any combinations of characters in general.

i find "FIND, MID, MATCH, RIGHT, ..." doesn't do a good job, and
"data-TexttoColumn" can solve but it requires manual physical separation
and what i what is just to extract the content, thx!!

=LEFT(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filen
ame"))-FIND("[",CELL("filename"))-1),LEN(MID(CELL("filename"),FIND("[",CELL(
"filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1))-4)

"cola" wrote in message
...
=LEFT(MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filen
ame"))-FIND("[",CELL("filename"))-1),LEN(MID(CELL("filename"),FIND("[",CELL(
"filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1))-4)



what do you mean by "filename"? i cannot specify it because i suppose the
formula is to be generic for all different filenames that may be encountered
by different users. thx again.

Hi!

With the path/file name in A1:

=MID(A1,FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN
(SUBSTITUTE(A1,"\",""))))+1,FIND(".",A1)-1-FIND
("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE
(A1,"\","")))))

Biff

-----Original Message-----
C:\Program Files\Amersham Biosciences\ImageMaster 2D
Platinum
Trial\Gels\94-0005.mel


How to obtain 94-0005 in this case? certainly, the path
can also be d: and
the filename is of any combinations of characters in
general.

i find "FIND, MID, MATCH, RIGHT, ..." doesn't do a good
job, and
"data-TexttoColumn" can solve but it requires manual
physical separation
and what i what is just to extract the content, thx!!



.

"Biff" wrote in message
...
Hi!

With the path/file name in A1:

=MID(A1,FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN
(SUBSTITUTE(A1,"\",""))))+1,FIND(".",A1)-1-FIND
("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE
(A1,"\","")))))

Biff

-----Original Message-----
C:\Program Files\Amersham Biosciences\ImageMaster 2D
Platinum
Trial\Gels\94-0005.mel


How to obtain 94-0005 in this case? certainly, the path
can also be d: and
the filename is of any combinations of characters in
general.

i find "FIND, MID, MATCH, RIGHT, ..." doesn't do a good
job, and
"data-TexttoColumn" can solve but it requires manual
physical separation
and what i what is just to extract the content, thx!!


oh thx Biff :) but what's the meaning of '~'? and could you very briefly
describe what the expression you write mean? thx again for your kind
assistance.

--Ross

Hi!

...what's the meaning of '~'?

That's just an arbitrary character.

Here's how it works:

In your example, you want to extract a portion of the file
name. We know that the file name starts after the last
instance of "\", so we have to find that last instance.
Since we know the last instance is the one we're looking
for we simply count the number of "\" in the string:

LEN(A1)-LEN(SUBSTITUTE(A1,"\","")) = 5

Now we can look for the 5th instance of "\" and when we
find it we want to "mark that spot". So we use the
Substitute function to replace the last instance of "\"
with a unique character that will more than likely not
appear in the string. So I chose to use the tilde
character,"~". So we add the Substitute function to the
formula:

SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))

Now, this is what your string would look like after
processing that portion of the formula:

C:\Program Files\Amersham Biosciences\ImageMaster 2D
Platinum Trial\Gels~94-0005.mel

Now we tell the formula to look for the starting point of
the substring that we want to extract. That starting point
is the character after the "~":

=MID(A1,FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN
(SUBSTITUTE(A1,"\",""))))+1 = 74

Now we have to tell the formula how many characters to
return starting from the character after the "~". We want
to extract all the characters between the "~" and the ".".
WE know where the "~" is so all we need to do is find
the "." and subtract to get the number of characters we
want the formula to return. So we find the "." and
subtract 1 because we want the last character before
the ".":

FIND(".",A1)-1 = 80


Then subtract the location of the "~":

-FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE
(A1,"\","")))) = 73

So, 80 - 73 = 7

Put it all together:

Return the 7 character string starting from the 74th
character = 94-0005

Long winded but that's a pretty thorough explanation!

Biff

-----Original Message-----

"Biff" wrote in message
...
Hi!

With the path/file name in A1:

=MID(A1,FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN
(SUBSTITUTE(A1,"\",""))))+1,FIND(".",A1)-1-FIND
("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE
(A1,"\","")))))

Biff

-----Original Message-----
C:\Program Files\Amersham Biosciences\ImageMaster 2D
Platinum
Trial\Gels\94-0005.mel


How to obtain 94-0005 in this case? certainly, the path
can also be d: and
the filename is of any combinations of characters in
general.

i find "FIND, MID, MATCH, RIGHT, ..." doesn't do a good
job, and
"data-TexttoColumn" can solve but it requires manual
physical separation
and what i what is just to extract the content, thx!!


oh thx Biff :) but what's the meaning of '~'? and could
you very briefly
describe what the expression you write mean? thx again
for your kind
assistance.

--Ross


.

Hi!

Cola's formula will return the file name of the active
(open) workbook (file) provided that it's been saved and
named already.

Biff

-----Original Message-----

"cola" wrote in message
...
=LEFT(MID(CELL("filename"),FIND("[",CELL("filename"))
+1,FIND("]",CELL("filen
ame"))-FIND("[",CELL("filename"))-1),LEN(MID(CELL
("filename"),FIND("[",CELL(
"filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL
("filename"))-1))-4)



what do you mean by "filename"? i cannot specify it
because i suppose the
formula is to be generic for all different filenames that
may be encountered
by different users. thx again.


.

Brilliant, clapping.