Find the position of various characters in string

Hi all

Is it possible to find the location of different characters in a string (using VBA)?
Normally I would use InStr of InStrRev option in VBA but my situation is a little more complicated.

The character I am looking for is not always the same, sometimes it is a , or a . or a : or a "blank" etc

I tried to do it like:
dim strChar as string
strChar = "[,.? /]"

SearchPosition = InStr ("cell location", strChar) (searchposition is the name of the function I am trying to make)
This works if I define only 1 character, this way it gives me a 0 as outcome

I have tried to change it to strChar = "[,]" or strChar = "[.]" or strChar = "[ ]" but this doesn't work.

Can this be done or should I approach it in an other way?

Thanks in advance
Hein

Hi Andy

What I am looking for is the location of different characters.

e.g: "work,hobby" should return 5 (look for the location of the comma)
or: "working.hobby" should return 8 (the location of the dot)
or: 111,22 should return 4
or 111.22 should also return 4

The "dim strChar as string --> strChar = "[,.? /]" " part works if I use it as follows: "if searchPosition like "*" & strChar & "*" etc"

What I would like to do is to define all possible character that may occur in the string as strChar and use this in the InStr function

I have tried using replace , to. and / to . but can't get it to work either

Can you do anything with this

Hein

Hi Andy, MatrixMan

I think Matrixman´s code might be on to something
(BTW Andy: your example should return 2)

The code MatrixMan made needed some additional dim
I have tried to solve this by changing the code to (BTW: demo is the name of the function):
"
dim arrChars as Variant
arrChars = Array(".", ",", ":", "-")
dim i as long
For i = LBound(arrChars) To UBound(arrChars)
Demo =InStr(1, strYourString, arrChars(i), vbTextCompare)
Next i
"

This works, but only for the last defined characters, all other characters return a 0

Is there a way take that last obstacle?

Hein

If you want to edit the search string then

Sub test()
    Dim myStr As String, x, e, m As Object
    myStr = ",.?/"  '<-  alter this
    x = Array("work,hobby", "working.hobby", "111,22", "111.22", "1/2", "#$?!")
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "([\$\(\)\-\^\|\\\[\]\*\+\?\.])"
        myStr = .Replace(myStr, "\$1")
        .Pattern = "[" & myStr & "]"
        For Each e In x
            For Each m In .Execute(e)
                MsgBox "found """ & m.Value & """ in psotion " & _
                m.firstindex + 1, , e
            Next
        Next
    End With
End Sub

Hi Jindon

I think that your solution always assumes that the same text is used: ( x = Array("work,hobby", "working.hobby", "111,22", "111.22", "1/2", "#$?!")

In reality this varies. The string I am "investigating" are always numbers with a string format; actually they are times. eg. 1.11.11 of 11.11.1 or any combination of digits you can think of.

I think that MatrixMan's code may be the solution, although some adjusting is required.

Hein

PS
Of course thanks you for your help, much appriciated

You can loop through the cell or what ever you like to do.

It was just a sample to show how it works.

If you don't know how to do it.... just ask.

Hi Andy

Thanks big time!
Your solution gives exactly the outcome I was looking for.
Never thought that an (in my mind) simple question involved so much code.

Anyway: I can go further now.
Once again: Thank you

Hein