VBA(excel) numbers as string comparation

Question

How to compare numbers as string? I want to compare f.ex. 2 and 10. When i sort them in Excel in ascending order 2 is before 10, but when i compare in VBA those two 10 seems to be lower number than 2. I'm using strcomp function. I need to compare them as strings beacuse it's a part of bigger program that's searching for the identical strings in excel columns. Strings can be normal strings, numbers and number-ish strings as "12-131xxx".

The only way to do this is to pad them with leading zeros or treat numbers as numbers. Can you post your code? It's possible there is a work-around such as [using a regular expression](https://stackoverflow.com/q/22542834/4088852) instead. — Comintern, Mar 03 '19 at 17:33

score 0 · Accepted Answer · answered Mar 04 '19 at 04:13

Mnich, with StrComp, either vbBinaryCompare or vbTextCompare will give you the returns you're currently getting. When Excel sorts numbers/integers, it sorts as you would expect, but when sorting numbers as strings, it uses a textual comparison; so all numbers starting with 1, even 111, will be ranked lower than 2. As Comintern mentions, you have to add leading zeros to get away with this.

Or, perhaps, you replied one number comparison before you ranked the strings, you could try a Function to extract the numbers, then let that weigh your decision:

'pass in array from main stuff
Function StackOverflow(arr())

Dim arr_NewStr() As String
Dim x As Integer
Dim i As Integer
ReDim arr_NewStr(1 To UBound(arr))

x = 1

For i = 1 To UBound(arr)
    Do
        If IsNumeric(Mid(arr(i), x, 1)) Then
            arr_NewStr = arr_NewStr & Mid(arr(i), x, 1)
        End If
        x = x + 1
    Loop Until x = Len(arr(i))
Next

'clean up
x = vbEmpty: i = vbEmpty

'pass somewhere, or make "arr" public

End Function

VBA(excel) numbers as string comparation

1 Answers1