Find last occurance in a string

Tim Otero

unread,

Jun 25, 2003, 6:19:00 PM6/25/03

to

I need to find the last occurrence of a character within a string so I can
show only what is immediately following that character. For example:

blah\blah1\blah2\blah3

would return:

blah3

How would I go about doing this?

Thanks, in advance for the help,

tim

Ken Wright

unread,

Jun 25, 2003, 6:26:08 PM6/25/03

to

Question often asked to try and get last names from a string. With the string in A1:-

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
There's no 'I' in 'Team'
----------------------------------------------------------------------------

"Tim Otero" <timo...@msn.com> wrote in message news:#ENeIf2O...@TK2MSFTNGP11.phx.gbl...

Bob Kilmer

unread,

Jun 25, 2003, 6:29:38 PM6/25/03

to

In VBA,

MsgBox Mid("blah\blah1\blah2\blah3", InStrRev("blah\blah1\blah2\blah3",
"\") + 1)

"Tim Otero" <timo...@msn.com> wrote in message

news:%23ENeIf2...@TK2MSFTNGP11.phx.gbl...

Tim Otero

unread,

Jun 25, 2003, 6:40:04 PM6/25/03

to

Thanks Ken,

I guess I should have mentioned I need it in VBA. I took a look at Bob's
code, but that won't work because I'm getting a path (only path) from the
user through the use of the Windows Shell Browse For Folder (thank you Jim
Rech). I'd like to display the path in one location and the folder name in
another.

tim

"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message
news:Ow6IHj2O...@TK2MSFTNGP11.phx.gbl...

Tim Otero

unread,

Jun 25, 2003, 7:11:01 PM6/25/03

to

Ok, I take that back...didn't take good enough look at Bob's code...Works
beautifully.

tim

"Tim Otero" <timo...@msn.com> wrote in message

news:#Yni5q2O...@tk2msftngp13.phx.gbl...

Tim Otero

unread,

Jun 25, 2003, 7:11:19 PM6/25/03

to

Thanks for your help, Bob

tim
"Bob Kilmer" <rpr...@yahoo.com> wrote in message
news:u7BbEl2O...@tk2msftngp13.phx.gbl...

J.E. McGimpsey

unread,

Jun 25, 2003, 7:16:08 PM6/25/03

to

Bob's code works fine for the example you gave, but InStrRev was
introduced in XL00, so will only work in XL00/02. You can find VBA5
workaround at

http://support.microsoft.com/default.aspx?scid=kb;en-us;188007

In article <#Yni5q2O...@tk2msftngp13.phx.gbl>,

Bob Kilmer

unread,

Jun 25, 2003, 7:53:18 PM6/25/03

to

Glad it worked for you, Tim. BTW, the FileSystemObject in the Microsoft
Scripting Runtime (scrrun.dll) has all kinds of toys to parse filenames and
paths, manage files, etc.

Bob Kilmer

"Tim Otero" <timo...@msn.com> wrote in message

news:eiKLX82O...@TK2MSFTNGP11.phx.gbl...

Tim Otero

unread,

Jun 25, 2003, 7:59:20 PM6/25/03

to

Thanks for the info...I'll play around with them...should give me some good
ideas.

Thanks again,

tim

"Bob Kilmer" <rpr...@yahoo.com> wrote in message

news:uexG0T3O...@TK2MSFTNGP10.phx.gbl...

Ken Wright

unread,

Jun 26, 2003, 2:05:13 AM6/26/03

to

LOL

> I guess I should have mentioned I need it in VBA.

No you shouldn't - I should have twigged from which groups it was posted in. Been pretty brain
dead these last 3/4 weeks working a couple of big proposals, so I'm not really firing on all
cylinders at the moment - apologies.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
There's no 'I' in 'Team'
----------------------------------------------------------------------------

"Tim Otero" <timo...@msn.com> wrote in message news:#Yni5q2O...@tk2msftngp13.phx.gbl...

ola

unread,

Jun 26, 2003, 11:25:34 AM6/26/03

to

Public Function test_str(ts1 As String, ch As String) As
String
Dim fpos As Long
Dim res As String

test_str = ""
fpos = InStr(1, StrReverse(ts1), ch)
If (fpos > 0) Then test_str = Right(ts1, fpos - 1)

End Function

Bob Kilmer

unread,

Jun 26, 2003, 6:11:44 PM6/26/03

to

No better, and maybe worse than the InStrRev option, but ... well, it
occurred to me. Requires Split which came to VBA late as well.

Sub Hmmmm()
Dim v As Variant
v = Split("blah\blah1\blah2\blah3", "\")
MsgBox v(UBound(v))
End Sub

"Tim Otero" <timo...@msn.com> wrote in message

news:%23ENeIf2...@TK2MSFTNGP11.phx.gbl...

Tim Otero

unread,

Jun 26, 2003, 6:34:25 PM6/26/03

to

Wow!
I need to tear that one apart and figure out what the heck it is doing...

Thanks for the info
This is fun stuff

tim
"Bob Kilmer" <rpr...@yahoo.com> wrote in message

news:OIgUu$CPDHA...@TK2MSFTNGP11.phx.gbl...

Bob Kilmer

unread,

Jun 27, 2003, 6:39:58 PM6/27/03

to

Split returns an array parsed by the backslashes. UBound returns the upper
bound of an array, a.k.a., the index to the last item. Hence v(Ubound(v))
retruns the last item. If you knew ahead of time that you wanted the 4th
item (index = 3) you could write

Msgbox Split("blah\blah1\blah2\blah3","\")(3)

Could also write

s = "blah\blah1\blah2\blah3"
Msgbox Split(s,"\")(Ubound(Split(s,"\")))

, to retun the last item, but now we're really getting carried away! <g>

Bob

"Tim Otero" <timo...@msn.com> wrote in message

news:OsaoaMDP...@TK2MSFTNGP11.phx.gbl...

Tim Otero

unread,

Jun 27, 2003, 7:08:23 PM6/27/03

to

Thanks again Bob,

One last question, if I may. I understand your explanation, I'm just
confused as to what function the initial v performs.

Ubound(v) returns 3
v(Ubound(v)) returns Blah3

which is what I wanted. Is this just one of those things, or is there a good
reason this works?

tim

"Bob Kilmer" <rpr...@yahoo.com> wrote in message

news:#gkkK0PP...@tk2msftngp13.phx.gbl...

Bob Kilmer

unread,

Jun 27, 2003, 8:26:54 PM6/27/03

to

v is just a Variant variable to which you assign the array returned by
Split. Assigning an array to a variable is (or was) a little unusual in
Visual Basic. Split is relatively new to VB. You might look into the Array()
function, also.

You can define arrays of various types. The following is a more typical way
to declare and use arrays in VB. They are often, perhaps usually, populated
in a loop of some kind because they can be quite large or because it just
makes sense to do so in context, rather than by explicit assignment as
below. Often you are collecting things together that make sense as a group
and you want easy access to (by index) later. You can create
multi-dimensional arrays as well. You can think of a two dimensional array
as you might a spreadsheet - that is as having rows and columns.

Sub test()
Dim myArray(0 To 2) As Long
myArray(0) = 1000
myArray(1) = 2000
myArray(2) = 3000
Msgbox CStr(myArray(2))

Dim Pets(0 To 2) As String
Dim i as Integer
Pets(0) = "Cat"
Pets(1) = "Dog"
Pets(2) = "Fish"
For i = LBound(Pets) To UBound(Pets)
Msgbox myArray(2)
Next i
End Sub

Bob

"Tim Otero" <timo...@msn.com> wrote in message

news:ePmKDEQ...@TK2MSFTNGP10.phx.gbl...

Tim Otero

unread,

Jun 30, 2003, 1:15:46 PM6/30/03

to

Thanks again for all the information and all your help. In the pets sub I
changed myArray(2) to Pets(i) to display all the pet names. Very cool. This
gives me lots of ideas.

tim
"Bob Kilmer" <rpr...@yahoo.com> wrote in message

news:#Dzn6vQP...@TK2MSFTNGP11.phx.gbl...