Find Character Position in String

  • Thread starter Thread starter SportsDave
  • Start date Start date
S

SportsDave

Hi there,

I am writing some code in Excel VBA, and I need to find the position of
a colon in a string variable. I thought I would be able to use the
'find' function to return the position value as I would on a normal
worksheet. However, VBA doesn't seem to recognise the function ("Sub or
Function not Defined"). I have also checked the help files and there
seems to be no mention of it. Could somebody please advise me on an
alternative.

Many thanks,

Dave
 
Hi SportsDave,

Try something like:

'=============>>
Public Sub Tester()
Dim sStr As String
Dim pos As Long

sStr = "test;String"

pos = InStr(1, sStr, ";", vbTextCompare)

MsgBox pos
End Sub
'<<=============
 
if the string is typed in A1 then something like the below will do it:
hopethis helps
J

Sub FindColon()

Dim SearchString, SearchChar, MyPos

SearchString = Cells(1, 1) 'range("A1") contains the string
SearchChar = ":" 'Search for ":".

MyPos = InStr(1, SearchString, SearchChar, vbTextCompare)

MsgBox "The colon was character: " & MyPos

End Sub
 
Dim s as String, iloc as Long
s = "Some string with a : contained within"
iloc = Instr(1,s,":",vbTextCompare)
 
Back
Top