Pulling just the filename

C

Cardinal

I have a column in my spreadsheet that has a bunch of image names and
paths. Example: images/taapay-040.gif

Is there a way that I can pull out just the filename? This would make
things so much easier for me and save me a ton of editing. Thank you
very much.
 
B

Bob Phillips

=MID(A1,FIND("/",A1,LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))+1,255)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
C

Cardinal

Oh boy, my fault - please forgive me. Your function worked perfectly
although I now realize that I left out one important item. Not all the
paths are the same. They might one level or several levels deep. All of
these are possibilites:

images/taasec-004.gif
data/images/taasec-004.gif

Thanks and sorry for forgetting this in my original post.
 
G

Gord Dibben

If the hyphen is common to all, try Data>Text to Columns with the hyphen as
delimiter.


Gord Dibben MS Excel MVP
 
B

Bob Phillips

=MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+
1,255)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
R

Ron Rosenfeld

I have a column in my spreadsheet that has a bunch of image names and
paths. Example: images/taapay-040.gif

Is there a way that I can pull out just the filename? This would make
things so much easier for me and save me a ton of editing. Thank you
very much.
Click to expand...

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=REGEX.MID(A1,"(?<=/)[^/]+$")

to return the last string that follows a "/"
--ron
 
H

Harlan Grove

Ron Rosenfeld wrote...
....
Then use the formula:

=REGEX.MID(A1,"(?<=/)[^/]+$")

to return the last string that follows a "/"
Click to expand...

You don't need the assertion. The following is sufficient.

=REGEX.MID(A1,"[^/]+$")
 
R

Ron Rosenfeld

Ron Rosenfeld wrote...
...
Then use the formula:

=REGEX.MID(A1,"(?<=/)[^/]+$")

to return the last string that follows a "/"
Click to expand...

You don't need the assertion. The following is sufficient.

=REGEX.MID(A1,"[^/]+$")
Click to expand...

Thanks.
--ron
 
Top