VBA not reading correct 2 strings of data

[alexgoaga]

Hey guys, I have a problem and i don't understand where is the issue.

There are 2 simple texts : on sheet 1 in A1 cell : i1​86​59 and in sheet 2 in A1 cell : i18680 , and i want to simple compare them who's the bigger one by using the following code :

If Left(Worksheets(1).Cells(1, 1), 1) = Left(Worksheets(2).Cells(1, 1), 1) Then ' This line checks if the first letter is the same

number1= Right(Worksheets(1).Cells(1, 1), 5)
number2= Right(Worksheets(2).Cells(1, 1), 5)

If number1 - (number2+ 1) > 0 Then

msgbox number1 & "is higher"

endif

endif

The issue is that the first number in debugger is read as : "i1?86?59" . What is with the "?" sign ? Why is displayed on the 1st string and not on the other one?

I tried anything i found on google , using INT , causes error, and VAL returns 1.

**** The 1st text is received and formated from copying data from another excel.

[Arkadi]

possibly there are some special characters in the value from sheet1? Just a guess...

If I type your numbers into sheet1 and 2, it works fine, but if I COPY your number for the first value and paste it in Excel, I get the same results as you.

[alexgoaga]

how...I can make a script to fix it or is none ?

[Arkadi]

Is your goal to run this on a large set of data? you could just fix the cell(s) manually if it is very little, or we can use code to fix it, but:
Do you want code to go fix the data on the sheet?
Or do you want the code to just ignore the (what I am guessing are) non-printable characters?

[alexgoaga]

i dont know what is the difference between the 2 options : If the "?" sign disappears doesnt fix the issue to the remaining things : i1​86​59 vs i18680 => 1​86​59 > 18680?

[Arkadi]

Ok well this code would work but one caveat: It will always ignore question maks (real ones just like these weird ones you have now) in any of the strings when performing the Right function. If that does not interfere with your use then it should work:

Sub test()

If Left(Worksheets(1).Cells(1, 1).Value, 1) = Left(Worksheets(2).Cells(1, 1).Value, 1) Then ' This line checks if the first letter is the same
    number1 = Right(CleanUp(Worksheets(1).Cells(1, 1).Value), 5)
    number2 = Right(CleanUp(Worksheets(2).Cells(1, 1).Value), 5)
    If number1 - (number2 + 1) > 0 Then MsgBox number1 & "is higher"
End If

End Sub

Function CleanUp(myVal) As String

Dim i As Long

For i = 1 To Len(myVal)
    If Asc(Mid(myVal, i, 1)) <> 63 Then CleanUp = CleanUp & Mid(myVal, i, 1)
Next i

End Function

I tried using the CLEAN worksheetfunction, and simple Replace in VBA but could not get either to work.

[alexgoaga]

I tried to use the Clean option and copy-paste as values before but i didn't write them, sorry

About the cleanup function, works like a charm ! (Saved to favorites) !

Thanks, solved my problem !
Alex

[Arkadi]

Happy I could help out alxegoaga
Please remember to mark the thread as solved? Thanks!

P.S. Thanks for the rep