# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Find Character Position in String

## 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

----------


## Don Guillett

look in help index for

instr

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"SportsDave" <SportsDave.2862xy_1148230801.5597@excelforum-nospam.com> wrote
in message news:SportsDave.2862xy_1148230801.5597@excelforum-nospam.com...
>
> 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
>

----------


## Norman Jones

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" <SportsDave.2862xy_1148230801.5597@excelforum-nospam.com> wrote
in message news:SportsDave.2862xy_1148230801.5597@excelforum-nospam.com...
>
> 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
>

----------


## Norman Jones

Hi SportsDave,

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


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


---
Regards,
Norman

----------


## WhytheQ

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

----------


## Tom Ogilvy

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

--
Regards,
Tom Ogilvy


"SportsDave" <SportsDave.2862xy_1148230801.5597@excelforum-nospam.com> wrote
in message news:SportsDave.2862xy_1148230801.5597@excelforum-nospam.com...
>
> 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
>

----------


## SportsDave

Thanks a lot everyone. Help very much appreciated.

Dave

----------

