Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Francis Hayes (The Excel Addict)
 
Posts: n/a
Default Find nth instance of a character in a string

I'm sure I've done this in the past but for the life of me I can't remember
it now.

Say I have a string "http://www.theexceladdict.com/tutorials.htm" in cell
A1. I want to determine the position of the last "/" (forward slash). The
strings won't always contain the same # of "/"s.

I need to be able to do this as a formula and also in VBA code.

I appreciate your help.

--
Have a great day,
Francis Hayes (The Excel Addict)

http://www.TheExcelAddict.com
Helping Average Spreadsheet Users
Become Local Spreadsheet Experts
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

If you want to extract what's to the right of the last forward slash you can
use

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

if you want the position

=FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))


Regards,

Peo Sjoblom

"Francis Hayes (The Excel Addict)" wrote:

I'm sure I've done this in the past but for the life of me I can't remember
it now.

Say I have a string "http://www.theexceladdict.com/tutorials.htm" in cell
A1. I want to determine the position of the last "/" (forward slash). The
strings won't always contain the same # of "/"s.

I need to be able to do this as a formula and also in VBA code.

I appreciate your help.

--
Have a great day,
Francis Hayes (The Excel Addict)

http://www.TheExcelAddict.com
Helping Average Spreadsheet Users
Become Local Spreadsheet Experts

  #3   Report Post  
Jim Cone
 
Posts: n/a
Default

Francis,

And the following gets you the position using code ...
'-----------------------------------------------------------------------------------
Function LastPosition(ByVal strInput As String, ByVal strChars As String) As Long
'Jim Cone - San Francisco - Sep 18, 2003
'ByVal allows variants to be used for the string variables
On Error GoTo WrongPosition
Dim lngPos As Long
Dim lngCnt As Long
Dim lngLength As Long

lngPos = 1
lngLength = Len(strChars)

Do
lngPos = InStr(lngPos, strInput, strChars, vbTextCompare)
If lngPos Then
lngCnt = lngPos
lngPos = lngPos + lngLength
End If
Loop While lngPos 0
LastPosition = lngCnt
Exit Function

WrongPosition:
Beep
LastPosition = 0
End Function

'Call it like this...
Sub WhereIsIt()
Dim N As Long
N = LastPosition("http://www.theexceladdict.com/tutorials.htm", "/")
MsgBox N
End Sub
'----------------------------------------

Regards,
Jim Cone
San Francisco, USA


"Francis Hayes (The Excel Addict)"
wrote in message
...
I'm sure I've done this in the past but for the life of me I can't remember
it now.
Say I have a string "http://www.theexceladdict.com/tutorials.htm" in cell
A1. I want to determine the position of the last "/" (forward slash). The
strings won't always contain the same # of "/"s.
I need to be able to do this as a formula and also in VBA code.
I appreciate your help.
--
Have a great day,
Francis Hayes (The Excel Addict)
http://www.TheExcelAddict.com
Helping Average Spreadsheet Users
Become Local Spreadsheet Experts

  #4   Report Post  
Harlan Grove
 
Posts: n/a
Default

Peo Sjoblom wrote...
If you want to extract what's to the right of the last forward slash

you can
use

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))


You could also use MID and dispense with one of the LEN calls.

=MID(A1,FIND(CHAR(127),SUBSTITUTE(A1,"/",CHAR(127),
LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,1024)

Alternatively, using a defined name like seq referring to
=ROW(INDIRECT("1:1024")), this could be done with the array formula

=MID(A1,MAX(IF(MID(A1.seq,1)="/",seq))+1,1024)

if you want the position

=FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))

....

Using seq as above, this could be given by the array formula
=MAX(IF(MID(A1.seq,1)="/",seq))

  #5   Report Post  
Harlan Grove
 
Posts: n/a
Default

Jim Cone wrote...
And the following gets you the position using code ...
Function LastPosition(ByVal strInput As String, ByVal strChars As

String) As Long
....
'Call it like this...
Sub WhereIsIt()
Dim N As Long
N = LastPosition("http://www.theexceladdict.com/tutorials.htm", "/")
MsgBox N
End Sub

....

If one uses Excel 2000 or later, why this rather than a simple wrapper
around the InStrRev VBA6 function? If one uses Excel 5/95 or 97, why
not keep it simple?


Function foo(s As String, ss As String) As Long
Dim k As Long, n As Long

k = Len(ss)
n = InStr(1, s, ss)

If n 0 Then
foo = Len(s) - k

Do
foo = foo - 1
Loop Until Mid(s, foo, k) = ss Or foo <= n
Else
foo = n

End If

End Function



  #6   Report Post  
Jim Cone
 
Posts: n/a
Default

Hi Harlan,

I quickly ran three speed tests using Timer on
l00,000 loops on the string provide by Francis..
For five trials the average time was

Function Foo: 0.92 seconds
Function LastPosition: 0.83 seconds
Function RevInStr: 0.65 seconds (using 99 as lngStart)

The RevInStr function also comes from my private library :
'----------------------------------------------------------------
' Searches for a character, but starting at the end of the string.
' strString is the string you want to search in
' strChar is the character or string of characters you want to search for
' lngStart is the position in TheString you want to start the search at.
'----------------------------------------------------------------
Function RevInStr(ByRef strString As String, ByRef strChar As String, _
ByVal lngStart As Long) As Long

Dim lngNdx As Long
Dim lngLength As Long

lngLength = Len(strChar)
'If strChar length 1 this reduces number of loops required
If lngStart <= 0 or lngStart Len(strString) Then _
lngStart = Len(strString)- lnglength + 1

For lngNdx = lngStart To 1 Step -1
If Mid$(strString, lngNdx , lngLength) = strChar Then
RevInStr = lngNdx -1 ' or (lngNdx + lngLength) depending on which section you want
Exit For
End If
Next 'lngNdx
' In case nothing found or In case position found was 1 which would return 0.
If RevInStr = 0 then RevInStr = 1
End Function
'-------------------------------------------------

Have I overlooked something?

Regards,
Jim Cone
San Francisco, USA


"Harlan Grove" wrote in message
oups.com...
Jim Cone wrote...
And the following gets you the position using code ...
Function LastPosition(ByVal strInput As String, ByVal strChars As

String) As Long
...
'Call it like this...
Sub WhereIsIt()
Dim N As Long
N = LastPosition("http://www.theexceladdict.com/tutorials.htm", "/")
MsgBox N
End Sub

If one uses Excel 2000 or later, why this rather than a simple wrapper
around the InStrRev VBA6 function? If one uses Excel 5/95 or 97, why
not keep it simple?


Function foo(s As String, ss As String) As Long
Dim k As Long, n As Long
k = Len(ss)
n = InStr(1, s, ss)
If n 0 Then
foo = Len(s) - k
Do
foo = foo - 1
Loop Until Mid(s, foo, k) = ss Or foo <= n
Else
foo = n
End If
End Function


  #7   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Jim Cone" wrote...
I quickly ran three speed tests using Timer on
l00,000 loops on the string provide by Francis..
For five trials the average time was

Function Foo: 0.92 seconds
Function LastPosition: 0.83 seconds
Function RevInStr: 0.65 seconds (using 99 as lngStart)

....

Not my results.

Redirecting the output of the console command

dir c:\ /s/b

to a text file and loading that text file into Excel 2000 without parsing, I
used the first 40,000 filenames and iterated over them 10 times, so 400,000
calls for each function.

Here are my results. For me, foo is much faster than LastPosition.

------------------------------
foo 10.028
foo2 8.022
LastPosition 34.094
RevInStr 6.017
InStrRev 1.003
findrev 1.003
==============================


And here's my testing module.

'---------------------------------------------------------------------
Sub testem()
Const MAXITER As Long = 10, NUMROWS As Long = 40000

Dim r As Range
Dim s() As String, p() As Long
Dim i As Long, j As Long
Dim dt As Date, et As Date

On Error GoTo ExitProc
Application.Calculation = xlCalculationManual

Set r = ActiveSheet.Range("A1").Resize(NUMROWS, 1)

ReDim s(1 To NUMROWS)
ReDim p(1 To NUMROWS, 1 To 1)

For i = 1 To NUMROWS
s(i) = r.Cells(i, 1).Value
Next i

Debug.Print String(30, "-")

dt = Now
For i = 1 To MAXITER
For j = 1 To NUMROWS
p(j, 1) = foo(s(j), "\")
Next j
Next i
et = Now - dt
Debug.Print Format(et * 86640#, """foo ""0.000")

r.Offset(0, 2).Value = p

Erase p
ReDim p(1 To NUMROWS, 1 To 1)

dt = Now
For i = 1 To MAXITER
For j = 1 To NUMROWS
p(j, 1) = foo2(s(j), "\")
Next j
Next i
et = Now - dt
Debug.Print Format(et * 86640#, """foo2 "" 0.000")

r.Offset(0, 4).Value = p

Erase p
ReDim p(1 To NUMROWS, 1 To 1)

dt = Now
For i = 1 To MAXITER
For j = 1 To NUMROWS
p(j, 1) = LastPosition(s(j), "\")
Next j
Next i
et = Now - dt
Debug.Print Format(et * 86640#, """LastPosition ""0.000")

r.Offset(0, 6).Value = p

Erase p
ReDim p(1 To NUMROWS, 1 To 1)

dt = Now
For i = 1 To MAXITER
For j = 1 To NUMROWS
p(j, 1) = RevInStr(s(j), "\", 0)
Next j
Next i
et = Now - dt
Debug.Print Format(et * 86640#, """RevInStr "" 0.000")

r.Offset(0, 8).Value = p

Erase p
ReDim p(1 To NUMROWS, 1 To 1)

dt = Now
For i = 1 To MAXITER
For j = 1 To NUMROWS
p(j, 1) = InStrRev(s(j), "\")
Next j
Next i
et = Now - dt
Debug.Print Format(et * 86640#, """InStrRev "" 0.000")

r.Offset(0, 10).Value = p

Erase p
ReDim p(1 To NUMROWS, 1 To 1)

dt = Now
For i = 1 To MAXITER
For j = 1 To NUMROWS
p(j, 1) = findrev("\", s(j))
Next j
Next i
et = Now - dt
Debug.Print Format(et * 86640#, """findrev "" 0.000")

r.Offset(0, 12).Value = p

Debug.Print String(30, "=")

ExitProc:
Application.Calculation = xlCalculationAutomatic
Application.Calculate
End Sub


Function foo(s As String, ss As String) As Long
Dim k As Long, n As Long

k = Len(ss)
n = InStr(1, s, ss)

If n 0 Then
foo = Len(s) - k

Do
foo = foo - 1
Loop Until Mid(s, foo, k) = ss Or foo <= n
Else
foo = n

End If

End Function


Function foo2(s As String, ss As String) As Long
Dim k As Long, n As Long, p As Long

k = Len(ss)
n = Len(s) - k + 1

For p = n To 0 Step -1
If p 0 Then If Mid(s, p, k) = ss Then Exit For
Next p

foo2 = p
End Function


Function LastPosition( _
ByRef strInput As String, _
ByRef strChars As String _
) As Long
'Jim Cone - San Francisco - Sep 18, 2003
'ByVal allows variants to be used for the string variables
On Error GoTo WrongPosition
Dim lngPos As Long
Dim lngCnt As Long
Dim lngLength As Long

lngPos = 1
lngLength = Len(strChars)

Do
lngPos = InStr(lngPos, strInput, strChars, vbTextCompare)
If lngPos Then
lngCnt = lngPos
lngPos = lngPos + lngLength
End If
Loop While lngPos 0
LastPosition = lngCnt
Exit Function

WrongPosition:
Beep
LastPosition = 0
End Function


'----------------------------------------------------------------
' Searches for a character, but starting at the end of the string.
' strString is the string you want to search in
' strChar is the character or string of characters you want to search for
' lngStart is the position in TheString you want to start the search at.
'----------------------------------------------------------------
Function RevInStr( _
ByRef strString As String, _
ByRef strChar As String, _
ByVal lngStart As Long _
) As Long

Dim lngNdx As Long
Dim lngLength As Long

lngLength = Len(strChar)
'If strChar length 1 this reduces number of loops required
If lngStart <= 0 Or lngStart Len(strString) Then _
lngStart = Len(strString) - lngLength + 1

For lngNdx = lngStart To 1 Step -1
If Mid$(strString, lngNdx, lngLength) = strChar Then
RevInStr = lngNdx - 1
'or (lngNdx + lngLength) depending on which section
'you want
Exit For
End If
Next 'lngNdx
' In case nothing found or In case position found was 1 which
' would return 0.
If RevInStr = 0 Then RevInStr = 1
End Function


Function findrev(ss As String, s As String) As Long
findrev = InStrRev(s, ss)
End Function
'---------------------------------------------------------------------


Your RevInStr returns 1 less than all the other functions. I haven't
explored what might be needed to have it return the same values when there
are matches and 0 when there aren't. Returning 1 for both no match at all
and the only match at the beginning of strString is bad programming.

However, given the times for direct InStrRev and findrev, a simple wrapper
around InStrRev, I'll strengthen with my original statement to this: anyone
with VBA6 would a fool not to use VBA6's InStrRev.


  #8   Report Post  
Jim Cone
 
Posts: n/a
Default

Harlan,

I used your testing module on the 4700 files in
my Program Files folder.
The full file path was used for each file.
I ran the test 100 times for a total of 470,000 calls.
I repeated each set 5 times and the results are...

Name Time
LastPosition Average 7.4202
foo Average 4.6128
foo2 Average 4.6128
RevInStr Average 3.4092
findrev Average 0.6018
InStrRev Average 0.6018


Regards,
Jim Cone
San Francisco, USA


"Harlan Grove" wrote in message
...
"Jim Cone" wrote...
I quickly ran three speed tests using Timer on
l00,000 loops on the string provide by Francis..
For five trials the average time was

Function Foo: 0.92 seconds
Function LastPosition: 0.83 seconds
Function RevInStr: 0.65 seconds (using 99 as lngStart)


Not my results.

Redirecting the output of the console command

dir c:\ /s/b

to a text file and loading that text file into Excel 2000 without parsing, I
used the first 40,000 filenames and iterated over them 10 times, so 400,000
calls for each function.

Here are my results. For me, foo is much faster than LastPosition.
------------------------------
foo 10.028
foo2 8.022
LastPosition 34.094
RevInStr 6.017
InStrRev 1.003
findrev 1.003
==============================

-snip-
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I compare data in two worksheets to find matching cells? Gary Excel Discussion (Misc queries) 4 March 2nd 06 10:04 PM
Extract hyperlink string from excel cell Ryan Sapien Links and Linking in Excel 1 January 20th 05 01:24 AM
Excel has a "Find Next" command but no "Find Previous" command. Michael Fitzpatrick Excel Discussion (Misc queries) 2 January 11th 05 12:45 AM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 01:37 AM
Removing ' character from cells Don Excel Discussion (Misc queries) 5 December 21st 04 06:41 PM


All times are GMT +1. The time now is 11:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"