none
InStr function not matching text RRS feed

  • Question

  • Hello,

    I have a function using InStr that is not matching text correctly. I am trying to remove characters that can't be used in a URL:

    Public Function NewSEName(ID As Long, TitleIn As String) As String
        Dim strNewTitle As String
        Dim i As Integer
        Dim strOutput As String

        strNewTitle = TitleIn

        For i = 1 To Len(strNewTitle) 
            If InStr(i, "abcdefghijklmnopqrstuvwxyz1234567890 _-", Mid(strNewTitle, i, 1), vbTextCompare) > 0 Then 
                strOutput = strOutput & Mid(strNewTitle, i, 1)
            End If
        Next i
        NewSEName = Trim(strOutput)

    End Function

    Problem:

    Input: p-3-test-record-co-f-müserss-flutes-sef-ed-francais.aspx

    Output: p-3-tst-ror-----

    When the loop gets to the letter "e" it starts to not match. I've tried <> 0, vbBinaryCompare, vbDatabaseCompare, but no luck.

    Any help appreciated,

    Albert


    Albert S

    Saturday, May 4, 2019 6:44 PM

Answers

  •         If InStr(i, "abcdefghijklmnopqrstuvwxyz1234567890 _-", Mid(strNewTitle, i, 1), vbTextCompare) > 0 Then 

    Hi Albert,

    I think you gety better results if you change the first parameter from 'i' to '1':

            If InStr(1, "abcdefghijklmnopqrstuvwxyz1234567890 _-", Mid(strNewTitle, i, 1), vbTextCompare) > 0 Then …

    An other approach can be to loop through the characters of strNewTitle, and check whether the Acsii-values of thw character fall within certain limits.

    Imb.

    • Marked as answer by Albert S Saturday, May 4, 2019 8:16 PM
    Saturday, May 4, 2019 8:06 PM

All replies

  •         If InStr(i, "abcdefghijklmnopqrstuvwxyz1234567890 _-", Mid(strNewTitle, i, 1), vbTextCompare) > 0 Then 

    Hi Albert,

    I think you gety better results if you change the first parameter from 'i' to '1':

            If InStr(1, "abcdefghijklmnopqrstuvwxyz1234567890 _-", Mid(strNewTitle, i, 1), vbTextCompare) > 0 Then …

    An other approach can be to loop through the characters of strNewTitle, and check whether the Acsii-values of thw character fall within certain limits.

    Imb.

    • Marked as answer by Albert S Saturday, May 4, 2019 8:16 PM
    Saturday, May 4, 2019 8:06 PM
  • Hello,

    Thank you for the response! Yes, using 1 instead of i did the trick. I thought it might be something I overlooked.

    Take care,

    Albert


    Albert S

    Saturday, May 4, 2019 8:16 PM