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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top