1

I have strings that consist of leading dots followed by a number (for example "..2" or "....4". I want to delete all leading dots and convert the string into a long variable. So I have written a function that finds leading dots in strings and deletes them. For some reason, the function works for a string like "..2" but will not work for "...3". The InStr function will not find "." in "...3". The strings are read out from a column in a worksheet. They are not formatted in any weird way, I have tried just typing them in manually in a new worksheet without any changes to the default formatting settings, same results.

So I have tried several things. I beleive there must be some error involving character encodings, I cannot figure out how to solve this problem though. I have tried using a recursive function using InStr to delete the dots and then tried the split function with "." as the delimiter to test my assumption. Split has the same problem, works for "..2" but will not work for "...3". When I debug print the strings that I read out, "...3" seems to be formatted differently than "..2" or ".1". I do not know why. here you can see the difference in the formatting

Sub Gruppieren()
'read out strings first
'then try to delete the dots
Dim strArr() As String
Dim lngArr() As Long
Dim lLastRow As Long
Dim i As Long

lLastRow = getFirstEmptyRow("A", Tabelle1.Index) 
ReDim strArr(1 To lLastRow)
ReDim lngArr(1 To lLastRow)

For i = 1 To UBound(strArr)
 strArr(i) = Worksheets(1).Cells(i, 1).Value
 Debug.Print strArr(i)
 strArr(i) = clearLeadingDots(strArr(i))
 'strArr(i) = splitMeIfYouCan(strArr(i))
 If IsNumeric(strArr(i)) = True Then
  lngArr(i) = CLng((strArr(i)))
  Debug.Print lngArr(i)
 End If
Next i
End Sub

'The functions:

Function clearLeadingDots(myText As String) As String
 Dim i As Long

 i = InStr(myText, ".")
  If i <> 0 Then
    myText = Right(CStr(myText), Len(myText) - i)
    clearLeadingDots = clearLeadingDots(CStr(myText))
  Else
    clearLeadingDots = CStr(myText)
    Exit Function
  End If
End Function

Function splitMeIfYouCan(myText As String) As String
 Dim myArr() As String
 Dim i As Long
 myArr = Split(myText, ".")

 splitMeIfYouCan = myArr(UBound(myArr))
End Function

Edit: The answer was, that three dots were converted into an ellipsis automatically, searching for and eliminating Chr(133) did the job.

MYZ
  • 331
  • 2
  • 10
  • 4
    Maybe it's changed to ellipsis (...)? Try char(133). – Egan Wolf Mar 29 '19 at 07:30
  • 1
    @EganWolf: I believe you are right. `Chr(133)` finds it :) – Siddharth Rout Mar 29 '19 at 07:33
  • 1
    Thank you very much, `Chr(133)` did the job! – MYZ Mar 29 '19 at 07:46
  • 1
    Was browsing through your old/open questions, for this particular one you could also use `RegExp` lib to avoid these functions. Also, you might not need VBA at all as it can be done through a formula. – JvdV May 14 '20 at 11:50
  • @JvdV thank you for your effort! And thanks for telling me about `RegExp`. I have been doing a lot of string comparisons actually and this seems a very useful tool for that purpose. About the formulas: That is a part I really need to work on, making use of those formulas where they already exists, so I do not have to fire up a macro for everything! EDIT: For anyone reading this in the future: Look here for a really good answer explaining regular expressions in Excel VBA: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – MYZ May 15 '20 at 08:15

0 Answers0