[Semi-Resolved] MS Excel 2007: Get text after last "/"

[Tec-Nico]

Hello everyone, I am trying to see if I can get the text after the last instance of "/" inside of a text without having to use VBA... Is there a way to do this?

Thank you

[westconn1]

you can use =RIGHT(D21,LEN(D21)-FIND("\",D21)) where D21 is the cell with the text

[Tec-Nico]

Thank you for your response... I tried the formula you provided me but it returns the text after the first occurence of "/"

For example, in the following string:
"http://www.website.com/subsite/file.htm"

It will return "/www.website.com/subsite/file.htm" instead of "file.htm"

I know Excel is powerful by itself and I already have a macro that gets this part... I just want to know if there is a way using simple functions to achieve this.

Find and Search were good candidates for achieving this but I haven't found a way to do this, I will appreciate your further assistance.

[Siddharth Rout]

For example, in the following string:
"http://www.website.com/subsite/file.htm"

It will return "/www.website.com/subsite/file.htm" instead of "file.htm"

Try this..

Private Sub CommandButton1_Click()
String = "http://www.website.com/subsite/file.htm"
temp = ""
For i = Len(String) To 1 Step -1 'ensure search takes from the end of string
     'Searching the string
     If Mid(String, i, 1) <> "\" Then
         temp = Mid(String, i, 1) + temp
     Else
         'exit if "\" found
         Exit For
     End If
Next i
 
'This will give you "file.htm"
MsgBox temp
 
End Sub

Hope this helps...

[Siddharth Rout]

Hi

If you want it as a function then paste this in a module

'Your very own function

Private Function TeckNico(str1 As String) 
 
Dim temp As String 
Dim i As Integer 
 
temp1 = Trim(str1) 
temp = "" 
 
For i = Len(temp1) To 1 Step -1 
    If Mid(temp1, i, 1) <> "\" Then 
        temp = Mid(temp1, i, 1) + temp 
    Else 
        Exit For 
    End If 
Next i 
 
TeckNico = temp 
 
End Function

If Cell A1 = "http://www.website.com/subsite/file.htm"
enter this formula in say Cell C2

=TeckNico(A1)

The result...
"file.htm"

Hope this helps...

[RobDog888]

@koolsid, he posted that he didnt want to use VBA code.

You may not be able to do this just as a formula since there is no InstrRev formula function and that would require a loop to get the last instance of the forward slash character.

[Siddharth Rout]

@koolsid, he posted that he didnt want to use VBA code.

i do agree that he doesn't have to use VBA code so i created a function for him...

like you correctly said

there is no InstrRev formula function and that would require a loop to get the last instance of the forward slash character.

also there is no function which will reverse the text for example "abc/def/ghi" to "ihg/fed/cba" so that we could have used westconn's function...

[Tec-Nico]

Thanks, Koolsid and Rob.

Just as Rob pointed, I wanted to avoid using VBA and was hoping to see if there was a way to do it without it... Nevertheless, thank you for your time and effort.

This is the code I am using in case any of you were interested:

Private Function getShortName(strName As String) As String
 Dim iLastIndex As Integer, t As Integer
 
 iLastIndex = 0
 t = InStr(iLastIndex + 1, strName, "/")
 
 Do While t > 0
   iLastIndex = t
   t = InStr(iLastIndex + 1, strName, "/")
 Loop
 
 getShortName = Mid$(strName, iLastIndex + 1)
End Function

Added: Using it as a function directly on the Cell would not work if the Macros are disabled anyway, right?

[westconn1]

it can be done using nested finds within if statements, the code below will return the second instance of \ if it exists

=RIGHT(D21,LEN(D21)-IF(ISERROR(FIND("\",D21,FIND("\",D21)+1)),FIND("\",D21)+1,FIND("\",D21,FIND("\",D21)+1)))


you would have to nest for all possible occurances of \ (very messy)
unfortunately find returns an error if the find string is not found, unlike InStr which returns 0, so find should always be used within an if satatement otherwise an error will occur if there is no instance of the find string

[RobDog888]

But then you are also limited to 7 levels of nesting as its Excel's limit. If the string has more occurances of the forward slash then it will not return the proper string needed.

[Tec-Nico]

Nice discussion we are having here about Excel's capabilities for something like this... Don't you agree?

Rob is right about the levels, you never know if there might be more than 7 nested "/" and since we can't control that and is really important to get only the name without the path since that "nickname" is used for more complex operations.

I am still wondering if using the function approach will provide to be useless in a Macro disabled Excel document... I think the most probable answer is "yes, it will be". However, it is worth thinking about it.

[westconn1]

there is an option to trust all installed addins and templates, in the security settings, maybe that will work for you

you can shorten your function down to one line

Function myfilename(cel As Range) As String
myfilename = Right(cel, Len(cel) - InStrRev(cel, "\"))
End Function

[RobDog888]

This is what I almost posted before until I realized the real issue.

Dim str As String
str = "http://www.website.com/subsite/file.htm"
MsgBox (Mid$(str, InStrRev(str, "/") + 1)) 'file.htm

You can get e Digital Cert for about $100 for non-private use to sign your workbook with. This will aid in allowing the macros to run with out the prompt to enable macros, or optionally added together with an AddIn or just checking the box to Trust the addin.

[Tec-Nico]

Thank you, Rob and westconn. I really appreciate your advice, the file would be used inside our own company but some people would prefer to receive a macro-less document instead... Some of the business users would not be happy having to change their Macros' settings either.

However, I think I will keep Rob's code for the function and will try to just execute it once before publishing the Excel Sheet.

Thanks again.

I just wonder if I should add "Resolved" to this thread even when there is no solution doing it without VBA.

[RobDog888]

You could label it [Semi-Resolved] That will note its got some kind of solution but may be still wanting other input.

[Siddharth Rout]

Hi

If you are still interested here is a shorter code

paste this in a module

Function reverse(str1 As String) As String
 
    reverse = StrReverse(str1)
 
End Function

use this formula

=reverse(LEFT(reverse(A2),FIND("/",reverse(A2),1)-1))

where cell A2="http://www.website.com/subsite/file.htm"

[RobDog888]

No thats longer as you will have to use a longer formula. Mine can be done with just this call to the function passing the cell reference.

'Formula for the cell
=MyFind(B1)
 
'VBA Module code
Public Function MyFind(MyCell As Range)
    MyFind = (Mid$(MyCell, InStrRev(MyCell, "/") + 1))
End Function

Then if you want to get fancy you can add error trapping in case the cell doesnt contain any forward slashes.

[Tec-Nico]

Thank you all for taking your time to help me. I have labeled the Thread "[Semi-Resolved]" but still feel satisfied with all your comments, code and inputs.

Rob: It is nice to see you again and glad to see all those titles you have, you deserve them. Hope to see you around the forums now that I am back to the r&d arena.

KoolSid: You have nice ideas and even made me think of new possibilities in other areas that might not be related to this, thanks.

WestConn: You provided the closest one to do it with pure Excel, thank you for your formulas.

See you around guys, oh... Anyone has experience with Word's QuickParts? and is this the correct forum to ask about them since they don't imply any VBA coding at all?

[westconn1]

as this is office development now, not vba i don't see why this is not the right forum, but i don't know the term QuickParts

[Tec-Nico]

Thanks, WestConn. QuickParts is a new concept added in Word 2007 where you can store parts of your document in a library and then insert them later (Simple version of it, I know... ).

[RobDog888]

It would probably be good to start a new thread on QuickParts.

wdTypeQuickParts is new for Word 2007. Its a WdBuildingBlockTypes object. I couldnt find anything in Word 2007 help file or the Online help. Tried searching WdBuildingBlockTypes too. Nothing on Google that is related to Word either.

[Tec-Nico]

I know, I just wondered if it was the right forum before posting that thread. And again, you are right: There is very little documentation about it and that's the reason I would like to ask if anyone has played around with them.

[RobDog888]

I havent had too much time to play for a long time but been changing that lately. I didnt try a MS KB search though.

[Tec-Nico]

Thanks, Rob. I just started a new Thread about QuickParts: Word 2007 QuickParts and AutoNumbering

Oh, and I had a question regarding a FAQ element you added but didn't know if it was right to reply there and ask the question or to post a new thread about it, so I opted for the thread:
How to use .NET to make an Add-In for an Office Application.. That isn't in the list?