Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    edavis24601
    Guest

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

    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.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

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

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    edavis24601
    Guest

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

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    -Most, but NOT all the entries have the same number of before and after characters

    thanks!

    -Eric

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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,ce ll2),"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.]
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

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

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

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

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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) ))

  8. #8
    edavis24601
    Guest

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

    Wow...that's awesome. thanks, all!

    -Eric

  9. #9
    edavis24601
    Guest

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

    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) ))

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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:$1 024),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),ISBLAN K(A2)))}
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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)

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    Thanks, Bob. <A target="_blank" HREF=http://www.emailoffice.com/excel/>http://www.emailoffice.com/excel/</A> 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?
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    I don't understand "strip any number of selected characters"...?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •