Find Character Position in String

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

look in help index for

instr

--
Don Guillett
SalesAid Software
[email protected]
"SportsDave" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> SportsDave
> ------------------------------------------------------------------------
> SportsDave's Profile:
> http://www.excelforum.com/member.php...o&userid=34641
> View this thread: http://www.excelforum.com/showthread...hreadid=544078
>

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
'<<=============


---
Regards,
Norman



"SportsDave" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> SportsDave
> ------------------------------------------------------------------------
> SportsDave's Profile:
> http://www.excelforum.com/member.php...o&userid=34641
> View this thread: http://www.excelforum.com/showthread...hreadid=544078
>

Hi SportsDave,

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


For your purposes, repalce the shown semicolon string with a colon!


---
Regards,
Norman

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)

--
Regards,
Tom Ogilvy


"SportsDave" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> SportsDave
> ------------------------------------------------------------------------
> SportsDave's Profile:
http://www.excelforum.com/member.php...o&userid=34641
> View this thread: http://www.excelforum.com/showthread...hreadid=544078
>

Thanks a lot everyone. Help very much appreciated.

Dave