count text differences between two cell (ranges) (Excel 2000)

Topic

New Reply

Hi,

Any thoughts on the best way to count the number of different characters between two cells?

Description:
I have two columns, let’s call them “before” and “after”. Most of the before and after fields have the same number characters, e.g., all phone numbers. I need to identify the number of differences between each individual before and after cell (e.g., (a3) contains 415.555.1212, (b3) is “after” and contains 415.555.1213, and so forth for a few thousand rows).

Also, some cells do have different numbers of characters (e.g., phone number plus area code or extension).

Suggestions on how to count per each cell, or possibly with an array formula?

thanks!

-Eric

p.s. I’m a newbie to this forum, so apologies if this is a question already covered elsewhere.

Reply | Quote

Replies

Eric

Welcome to this list and forum, here you will learn FAST…

OK if I understand you perfectly, you want to find out how many characters are in a cell. For that you can use the following:

=LEN(A3). This will return 12, and it is broken down as follows: 415 = 3, . = 4, 555 = 7, . = 8 1212 = 12.

he length function gets you the length of the string in the cell. Mind you if you have some formatting such as (415) 555-1212 but the actual number in the cell is 4155551212 then guess what the LEN will return?
You guessed it, it will return 10. The ( and ) are not part of the cell content.

Hope this helps.

Wassim

Reply | Quote

Wassim,

thanks for your reply, but it’s not quite what I’m trying to accomplish … sorry I wasn’t clear. I’m trying to find out how many characters are different between two cells. The cells may have exactly the same number of characters, it’s just that the characters are different.

I’ll give you an example of the output information I’m looking for:

(a3) contains ‘(415) 555-1212
(b3) contains ‘(415) 555-2121
Output– 4 characters were changed in (b3).
As you can see, although the total number of characters remains the same, four characters are different.

Also:

-I’d like to run this as a single formula array, but will do it for each individual cell and add them up if need be
-Most, but NOT all the entries have the same number of before and after characters

thanks!

-Eric

Reply | Quote

That’s kind of tricky because if the length has varied, you need to loop through a lot of permutations to see where a match might start. Here’s a function which will return the number of changed characters, BUT it will return zero if EITHER no characters have changed OR the LENGTH of the cell contents are different.

Function CHARCHANGECOUNT(ByVal Cell1 As Range, ByVal Cell2 As Range) As Long
Application.Volatile
Dim strCl1cont As String
Dim strCl2cont As String
Dim lngC1CharLength As Long
Dim lngCounter As Long
CHARCHANGECOUNT = 0
lngC1CharLength = Len(Cell1.Value)
strCl1cont = Cell1.Value
strCl2cont = Cell2.Value
If lngC1CharLength = Len(Cell2.Value) Then
For lngCounter = 1 To lngC1CharLength
If Mid(strCl1cont, lngCounter, 1) Mid(strCl2cont, lngCounter, 1) Then _
CHARCHANGECOUNT = CHARCHANGECOUNT + 1
Next
End If
End Function

I didn’t test this vigorously. Usage =CHARCHANGECOUNT(cell1,cell2).

For your purpose you might want to use: =IF(LEN(cell1)=LEN(cell2),CHARCHANGECOUNT(cell1,cell2),”DIFFERENT LENGTH”)

[Edit PS: Because of the potential problem with different cell content lengths, this doesn’t meet your request to have a single array formula for all pairs of cells.]

Reply | Quote

Eric

Sorry about that, I missed the point. OK so here you have to use some VBA I guess, I am not sure even an array formula will help you due to the fact that array formulas act on cell contents and you need to act on individual characters in the cell content. maybe the Find and Mach and InStr and Mid functions can be used to do what you want but I still think VBA will be easier.

Here is a simple example for looking at two cells and you can make it work for your range.

Sub FindTextDifferencies()
Dim sSourceCellContents As String ‘/Gets what is in the cell.
Dim sTargetCellContents As String ‘/Gets what is in the cell.
Dim iHowManyCharacters As Integer ‘/The Length of what is in cells.
Dim iCounter As Integer ‘/For-Next Counter.
Dim sDiffCharPosition As String ‘/Holds the postion of different characters.

sSourceCellContents = ActiveSheet.Range(“A1”).Value
sTargetCellContents = ActiveSheet.Range(“A2”).Value

If Len(sSourceCellContents) < Len(sTargetCellContents) Then
iHowManyCharacters = Len(sTargetCellContents)
Else
iHowManyCharacters = Len(sSourceCellContents)
End If

sDiffCharPosition = ""
For iCounter = 1 To iHowManyCharacters
If Mid(sSourceCellContents, iCounter, 1) Mid(sTargetCellContents, iCounter, 1) Then
‘/Do what you want when the characters are the same.
Else
sDiffCharPosition = sDiffCharPosition & iCounter & “, ”
End If
Next iCounter

MsgBox “I have found differences at these positions: ” & sDiffCharPosition
End Sub

Hope this helps. I guess it could be done via a formula, but this VBA was much faster.

Wassim

Reply | Quote

Sorry Eric

I was distracted at the most sensitive time.
Change to the following line:

If Mid(sSourceCellContents, iCounter, 1) = Mid(sTargetCellContents, iCounter, 1) Then

It should be equal not

Wassim

Reply | Quote

Array formula you want, here it is. Assume the cells are A1 and A2. try this (array-entered):
=SUM(1*(MID(A1,ROW(1:100),1)MID(A2,ROW(1:100),1)))

Reply | Quote

Wow…that’s awesome. thanks, all!

-Eric

Reply | Quote

One more question–what is the best way to have it not include blank cells? e.g., if (a2) was blank, but (b2) is not, then do not count (b2).

=SUM(1*(MID(A1,ROW(1:100),1)MID(A2,ROW(1:100),1)))

Reply | Quote

When you say “do not count B2″, do you mean if comparing B1 and B2 do not count the difference if B1 is blank but B2 isn’t (i.e., is your data running in vertical pairs across the sheet in two rows, or horizontal pairs down the sheet in two columns)? If so, this formula compares A1 and A2 but ignore if one of the pair is blank:

{=SUM(1*(MID(A1,ROW($1:$1024),1)MID(A2,ROW($1:$1024),1)))*NOT(OR(ISBLANK(A1),ISBLANK(A2)))}

I added the absolute references to the row numbers ($1:$1024) in case you need to copy the formula down because your data is in horizontal pairs going down the sheet; otherwise the row references drift. Bob Umlas may come up with a better way. While I was at it I had it ignore spaces if you also want that:

{=SUM(1*(MID(SUBSTITUTE(A1,” “,””),ROW($1:$1024),1)MID(SUBSTITUTE(A2,” “,””),ROW($1:$1024),1)))*NOT(OR(ISBLANK(A1),ISBLANK(A2)))}

Reply | Quote

Or a bit shorter:
=IF(COUNTA(A1:B1)2,””,SUM(1*(MID(A1,ROW(1:100),1)MID(B1,ROW(1:100),1))) )
(still array-entered)

Reply | Quote

Array formulas have never been a strength of mine. Meanwhile I’ve been evolving my Function a bit on a learn-as-I-go basis: it now takes an optional argument to ignore selected characters within the cells.

Function CHARCHANGECOUNT(ByVal Cell1 As Range, ByVal Cell2 As Range, _
Optional ByVal strIgnoreChrs As String) As Long
Application.Volatile
Dim strCl1cont As String, strCl2cont As String
Dim lngC1CharLength As Long, lngCounter As Long
CHARCHANGECOUNT = 0
lngC1CharLength = Len(Cell1.Value)
strCl1cont = StripTheseChars(Cell1.Value, strIgnoreChrs)
strCl2cont = StripTheseChars(Cell2.Value, strIgnoreChrs)
‘ commented lines can be enabled if cells with strings of different lengths are not to be processed
‘ If lngC1CharLength = Len(Cell2.Value) Then
For lngCounter = 1 To lngC1CharLength
If Mid(strCl1cont, lngCounter, 1) Mid(strCl2cont, lngCounter, 1) Then _
CHARCHANGECOUNT = CHARCHANGECOUNT + 1
Next
‘ End If
End Function

Function StripTheseChars(ByVal strInput As String, _
ByVal strChars2Del As String) As String
Application.Volatile
Dim lngPos As Long, lngInputSLen As Long, lngDelCharsLen As Long, lngCounter As Long
Dim strCurrentChar2Del As String
lngDelCharsLen = Len(strChars2Del)
For lngCounter = 1 To lngDelCharsLen
strCurrentChar2Del = Mid(strChars2Del, lngCounter, 1)
Do While InStr(strInput, strCurrentChar2Del)
lngInputSLen = Len(strInput)
lngPos = InStr(strInput, strCurrentChar2Del)
strInput = Left(strInput, lngPos – 1) & Right(strInput, lngInputSLen – lngPos)
Loop
Next lngCounter
StripTheseChars = strInput
End Function

Syntax = CHARCHANGECOUNT(Cell1,Cell2,” .()-“) where space, period, l&r parens and dashes are to be ignored, and the third argument is optional.

Reply | Quote

Given what you had to write to simulate a MUCH shorter array formula, I think it’d be advantageous to learn about array formulas. Check out
http://www.emailoffice.com/excel/arrays-bobumlas.html

Reply | Quote

Thanks, Bob. http://www.emailoffice.com/excel/ added to faves for future study.

The function work wasn’t as time consuming as you think: I had already written the called function a while back (you’ll see it can be used standalone). If you have time to answer (no doubt we both have actual work to do, the divisional President just walked in on me in the middle of writing this), how would you strip any number of selected characters from the cell contents with built-in Excel Functions?

Reply | Quote

I don’t understand “strip any number of selected characters”…?

Reply | Quote

(Please note disclaimer about actual work taking precedence.)

Let’s say you had a mix of International and US phone numbers with various combinations of plus signs, spaces, periods, parens and dashes, and you want to remove all plus signs, spaces, periods, parens and dashes from the cell contents for some other manipulation. Can you remove “+ .()-” from cell contents using built-in functions?

Reply | Quote

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”(“,””),”)”,””),”+”,””),” “,””),”-“,””),”.”,””)

Reply | Quote

Yuck.

Reply | Quote

Hey, it works! You CAN write a UDF, I suppose, but you wanted builtin.

Reply | Quote

Truedat!

Reply | Quote