I am trying to separate the last set of text in a long url address. I tried
=TRIM(RIGHT(A1,FIND("/",A1)+1)) and only got part of the text I need. I have
about 1000 lines of different url's that I want to do this to so wondering if
there's a way. Below is an example:

http://chs.com/US/NATIONAL/KDSDR.NSF/53c9f3d7e14ba67e85256c7d005753cc/281e54422921e0a985256d1a000162cf/$FILE/Submission Form-Tax Record - 2008-02-01.doc

Thanks, Lisa

Try this formula...

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))

--
Rick (MVP - Excel)


"Lisa W" wrote in message
...
I am trying to separate the last set of text in a long url address. I
tried
=TRIM(RIGHT(A1,FIND("/",A1)+1)) and only got part of the text I need. I
have
about 1000 lines of different url's that I want to do this to so wondering
if
there's a way. Below is an example:

http://chs.com/US/NATIONAL/KDSDR.NSF/53c9f3d7e14ba67e85256c7d005753cc/281e54422921e0a985256d1a000162cf/$FILE/Submission
Form-Tax Record - 2008-02-01.doc

Thanks, Lisa

When you say "last set of text" are you referring to the file name
"Submission Form-Tax Record - 2008-02-01.doc"?

If so, try this:

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

HTH
Elkar



"Lisa W" wrote:

I am trying to separate the last set of text in a long url address. I tried
=TRIM(RIGHT(A1,FIND("/",A1)+1)) and only got part of the text I need. I have
about 1000 lines of different url's that I want to do this to so wondering if
there's a way. Below is an example:

http://chs.com/US/NATIONAL/KDSDR.NSF/53c9f3d7e14ba67e85256c7d005753cc/281e54422921e0a985256d1a000162cf/$FILE/Submission Form-Tax Record - 2008-02-01.doc

Thanks, Lisa

"Rick Rothstein" wrote...
Try this formula...

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))
....

The last piece of the url could include a long string or parameters
fed to the url's server, so it's possible (though perhaps unlikely)
that it could be more than 99 chars.

The guaranteed way to find the last / char in a string s is

FIND(CHAR(127),SUBSTITUTE(s,"/",CHAR(127),LEN(s)-LEN(SUBSTITUTE
(s,"/",""))))

so the string to the right of it would be given by

=REPLACE(s,1,FIND(CHAR(127),SUBSTITUTE(s,"/",CHAR(127),
LEN(s)-LEN(SUBSTITUTE(s,"/","")))),"")

An shorter alternative involves using a defined name like seq which
refers to a formula like

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1024,1))

in a cell formula like

=REPLACE(s,1,MATCH(2,1/(MID(s,seq,1)="/")),"")

Then again, of the OP needs to do this often, better to use better
tools like Laurent Longre's MOREFUNC.XLL add-in which provides regular
expression add-in functions, which could deal with this problem with
just

=REGEX.SUBSTITUTE(s,".*/","")

Try this formula...

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))
...

The last piece of the url could include a long string or parameters
fed to the url's server, so it's possible (though perhaps unlikely)
that it could be more than 99 chars.

What if we just increase the number of spaces? Perhaps...

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255))

--
Rick (MVP - Excel)

"Rick Rothstein" wrote...
....
What if we just increase the number of spaces? Perhaps...
....

It's not GUARANTEED to work. Neither is substituting a 'magic'
character for the last / then searching for it, but it's arguably more
reliable. The only truly reliable ways to search for the last / in a
string using only built-in functionality (so no add-ins or udfs) is
the MATCH(2,1/(MID(s,ROW(1:32667),1)="/")) approach.

This is one place where OpenOffice Calc is well ahead of Excel. This
could be done in OOo Calc with

=REPLACE(A1;1;SEARCH("[^/]+$";A1)-1;"")

Harlan Grove wrote...
....
=REPLACE(A1;1;SEARCH("[^/]+$";A1)-1;"")

I'll shorten this before someone else beats me to it.

=REPLACE(A1;1;SEARCH("/[^/]+$";A1);"")

What if we just increase the number of spaces? Perhaps...
...

It's not GUARANTEED to work.

Well, technically, true in the general case; but I would think if the user
had a handle on the type of text he/she would be processing, then it could
be fine to use it. For example, the OP appeared to be obtaining a filename
from the end of the URL. Filenames should not be longer than 256, but I've
never seen one anywhere near that long in my entire computer career (which
goes back to the early 1980s). I'm guessing there are some practical limits
on what to expect in the length of a URL as well (I'm guessing the 260
character limit wouldn't apply, but I don't think I have ever seen one even
approaching 500 characters). It would seem that in all but a most extreme,
anomalous situation that using 255 spaces in the formula I posted should
work fine. I would be surprised if the OP could not use the formula I posted
with complete confidence.

--
Rick (MVP - Excel)

Thanks for all your suggestions - I tried out
=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))
and it worked so I didn't try out the other suggestions.

No surprise to Harlan but I am extremely grateful! Allowed me to do
something impressive to my bosses and save quite a bit of time.

Thanks again, Lisa