Finding the position of a LIKE match in a string??? 1

[knifey]

Hi,
Is it possible to get the position of a pattern match in a string that has been found using the LIKE operator?
This is what I have so far.....
Function GetDateFromString(strVar As String) As String
Dim strVarLen As Long
strVarLen = Len(strVar)
If strVar Like "*##.##.##*" Then
end if
End Function
All I need now is a way to tell the position of the pattern match so I can extract using MID, LEN. But this is proving more difficult than I'd imagined.
Any advice or pointers would be much appreciated.
Thanks,
K

 

[SkipVought]

Hi,

Do you have several typical examples of such strings?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

You could try:

Function GetDateFromString(strVar As String) As String
Dim i As Long
If strVar Like "*##.##.##*" Then
For i = 1 To Len(strVar) - 8
  If Mid(strVar, i, 8) Like "##.##.##" Then
    Exit For
  End If
Next
  GetDateFromString = i
Else
GetDateFromString = 0
End If
End Function

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

And why would you want to return a STRING for a DATE, if that's what the purpose is?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Of course if you want the actual string, you could use something like:

Function GetDateFromString(strVar As String) As String
Dim i As Long
If strVar Like "*##.##.##*" Then
For i = 1 To Len(strVar) - 8
  If Mid(strVar, i, 8) Like "##.##.##" Then
    Exit For
  End If
Next
  GetDateFromString = Mid(strVar, i, 8)
Else
  GetDateFromString = "N/A"
End If
End Function

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

Modifying Paul's code accordingly to return a DATE if indeed there is a string that represents a date...

Function GetDateFromString(strVar As String) As Variant
    Dim i As Long, a
    If strVar Like "*##.##.##*" Then
        For i = 1 To Len(strVar) - 8
          If Mid(strVar, i, 8) Like "##.##.##" Then  '[b]
            a = Split(Mid(strVar, i, 8), ".")[/b]
            Exit For
          End If
        Next
'[b][highlight]assuming that ##.##.## is yy.mm.dd structure[/highlight]
          GetDateFromString = DateSerial("20" & a(0), a(1), a(2))
[/b]        Else
        GetDateFromString = 0
    End If
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Skip,

I wasn't about to assume anything about the data order in the string, or the century to which it belongs.

Cheers
Paul Edstein
[MS MVP - Word]

 

[N1GHTEYES]

How about:

Public Function GetDate(strvar As String) As Variant
Dim pos As Long
GetDate = "No Date found"
pos = 0
If strvar Like "*##.##.##*" Then
    Do
        pos = InStr(pos + 1, strvar, ".")
        GetDate = Mid(strvar, pos - 2, 8)
    Loop While Not GetDate Like "##.##.##"
End If
End Function

Tony

 

[N1GHTEYES]

Hmm, something weird going on here. Just before I posted my reply, there were no responses, but I seem to have come in after a whole mailbag from Skip & Macropod. Sorry chaps.

Bearing in mind Skip's comment about dates, you could do the same to the code I posted.

Note, this only works if your date really is in the format ##.##.##. So it would miss 7.9.02, or 10/11/92.

Tony

 

[SkipVought]

Just an axample. I hate 2-digit year shortcuts. You have to test the value of yy if .LT. some value then assume twentyfirst century otherwise twentieth.

But of course, it is even possible that the ##.##.## could represent a date that is neither in this century or the last, in which case a date in Excel, MIGHT be unrepresentable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

'Like' uses a bastardised version of regular expressions - so why not use the full regexp functionality.

For example:

[blue]Public Function GetMatchPosFromString(strVar As String) As String
    Dim myitem As Object
    
    GetMatchPosFromString = -1 ' so we get similar results as Instr
    With New RegExp
        .Pattern = "\d\d\.\d\d\.\d\d"
        For Each myitem In .Execute(strVar)
            GetMatchPosFromString = myitem.FirstIndex
        Next
    End With
    
End Function[/blue]

 

[SkipVought]

non illegitimi carborundum ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Don't let the b*st*rds grind you down. Not heard, or even thought of, that one for years!!!!!!!

Many thanks,
D€$

 

[SkipVought]

it was a rather obtuse reference to strongm's post.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[knifey]

Hi,
All worked great but I've gone fopr macropod's version.
Thanks again,
K

 

[SkipVought]

What do you intend to do with the position value?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

@Skip

 

[SkipVought]

how I am wired

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}