Compare strings

[jpaokx]

Hello,

I have two cases here with Strcomp function and I'll need your help.

When I compare two strings using the above function, I get values as 0, 1, -1.

1) My first case is for the 0 values. I can normally get this even if I have two strings which ones is lower case and the other proper case.
For example,

Firstname OldFirstName Compare_Fields
Jack jack 0
Peter peter 0
Anna-Maria anna-maria 0
Thomas Thomas 0
Steven Steven 0

Clearly, the first 3 cases have been modified but the comparison is based on characters. However, I'll need to check the proper casing part (just like I can test it using the exact function in Excel)
So, for my testing/checking purposes, I'd like to compare even such case sensitive situations. Is there any way?



2) The other case is that, for the values 1 and -1, I'd like to see what character(s) was(were) changed. So, I'd like to apply a function that can give me which character(s) have been added (or removed).

For example,
Firstname OldFirstName Type_of_Change
Jack P jack P
Peter J peter J
Anna-Maria anna maria -
Thomas Thoms a
Lucy Luc_d y
Bruce B ruce
Steven Stvn ee

Is there any way that I can do it?
What I am trying to see is the type of corrections that I made to some strings and revise if necessary.

Thanks in advance!

 

[jdraw]

Not sure I'm answering you question:

see
https://support.office.com/en-us/article/StrComp-Function-a9a4531e-3b2a-4218-b139-f7442bc10b96

'The StrComp function has the following return values:

'If                             'StrComp returns
'
'string1 is less than string2       -1
'
'string1 is equal to string2         0
'
'string1 is greater than string2     1
'
'string1 or string2 is Null           Null

 

[jpaokx]

As explained above, the StrComp function is the one that I already used, but I noticed that what I did is that had omitted initially the comparison statement.
If I make a textual comparison (eg. argument is 1), then I get the same results while the binary one (e.g. argument is 0) will give me, the one that I need.
What I don't understand that is that the link states "If the third argument is 1, a textual comparison is performed; if the third argument is 0 or omitted, a binary comparison is performed". Isn't this wrong here? From my understanding, a binary comparison is only when the third argument is 0 (not omitted). Is that right?

Also, any recommendation about my 2nd query?

 

[jdraw]

I agree that if I omit the 3rd argument, I get a Text Compare.

Here is a sample using the immediate window:
?asc("a")
97
?asc("A")
65

? StrComp("a", "A", vbBinaryCompare)
1 <--------------indicates a > A (binaryCompare)

? StrComp("a", "A",vbTextCompare)
0 <--------------indicates a = A (textCompare)

? StrComp("a", "A")
0 <--------------indicates a = A (when compare option is omitted)

Here is a small test of compare where the strings are different.

'---------------------------------------------------------------------------------------
' Procedure : testcompare
' Author    : mellon
' Date      : 13/03/2016
' Purpose   : In 2 strings starting from the left,  show positions and values where the
' characters in these strings differ.
' This is a demo only.
'---------------------------------------------------------------------------------------
'
Sub testcompare()

'from https://support.office.com/en-us/article/StrComp-Function-a9a4531e-3b2a-4218-b139-f7442bc10b96

'M$oft vbconstants for compare
'vbUseCompareOption -1
'vbBinaryCompare 0
'vbTextCompare  1
'vbDatabaseCompare 2

'Return values from strCompare
' -1 where string1 < string2
'  0 where string1 = string2
'  1 where string1> string2
' Null where string1 or string2 is NULL

    Dim a As String
    Dim b As String
    Dim c As String
    Dim i As Integer
    Dim j As Integer

   On Error GoTo testcompare_Error

    a = "John Smith"
    b = "john smyth"
    Debug.Print a, b, StrComp(a, b)    'default is text compare
    For i = 1 To Len(a) - 1

        If Mid(a, i, 1) <> Mid(b, i, 1) Then
            Debug.Print "differ at position " & i & " where a has " & Mid(a, i, 1) & " and b has " & Mid(b, i, 1)
        End If
    Next i

   On Error GoTo 0
   Exit Sub

testcompare_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testcompare of Module ModuleTesting_CanKill"
End Sub

Result:

John Smith    john smyth    -1 
differ at position 8 where a has i and b has y

Good luck.