Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
string comparasion
Hello;
I have 2 strings I need to compare.. example... string #1 box of dates. string #2 boxes of dates. notice the extra es in string 2.. I need a way to identify what characters are extra as in... string 2 position 4 e position 6 s I know this can be done, but for the life of me I can not get what my boss wants... Can anyone help... thank in advance.. I am writing this as a basic excel macro (and I will accept it as a formula also if that is better) Excel 2003 (11.5612) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
string comparasion
See if this helps. There's no problem determining when the contents of the
two diverge - it's figuring out where they come 'back together' that gets a little tough. The reason I show a move of strTest1 and strTest2 into other variables is that the loops need to be controlled by the length of the shorter string. This will tell you that characters 4-6 of the longer string don't match up for your 2 test cases, but if the longer string was "boxes of dates, fresh" then it will tell you that everything from 4-15 doesn't match even though some of it does, it's just offset in there. Sub CompareStrings() Dim strTest1 As String Dim strTest2 As String Dim ShortString As String Dim LongString As String Dim LC As Integer Dim Counter As Integer Dim FirstBreakAt As Integer Dim SecondBreakAt As Integer strTest1 = "box of dates." strTest2 = "boxes of dates." 'move them into proper variables If Len(strTest1) <= Len(strTest2) Then ShortString = strTest1 LongString = strTest2 Else ShortString = strTest2 LongString = strTest1 End If For LC = 1 To Len(ShortString) If Mid(ShortString, LC, 1) < Mid(LongString, LC, 1) Then FirstBreakAt = LC Exit For End If Next Counter = 0 For LC = Len(ShortString) To 1 Step -1 If Mid(ShortString, Len(ShortString) - Counter, 1) _ < Mid(LongString, Len(LongString) - Counter, 1) Then SecondBreakAt = LC - 1 Exit For End If Counter = Counter + 1 Next If FirstBreakAt = 0 And SecondBreakAt = 0 Then MsgBox "Strings are duplicates" Else MsgBox "Characters " & FirstBreakAt _ & " through " & _ FirstBreakAt + SecondBreakAt _ & " of '" & LongString _ & "' don't match." End If End Sub "Sam Hodo" wrote: Hello; I have 2 strings I need to compare.. example... string #1 box of dates. string #2 boxes of dates. notice the extra es in string 2.. I need a way to identify what characters are extra as in... string 2 position 4 e position 6 s I know this can be done, but for the life of me I can not get what my boss wants... Can anyone help... thank in advance.. I am writing this as a basic excel macro (and I will accept it as a formula also if that is better) Excel 2003 (11.5612) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a) | Excel Programming | |||
Importing Long String - String Manipulation (EDI EANCOM 96a) | Excel Programming | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
Create a formula into a String then assign string to a cell | Excel Programming |