Formula Or Code To tell Me Difference between 2 Cells

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,748
Office Version
  1. 365
Platform
  1. Windows
I need a Formula Or Code To tell Me Difference between 2 Cells please. The example below shows that the difference in B2 to A2 is B299 and B3 to A3 is 8. With the result in C please. Thanks. N.B the difference could be anywhere in the cell.



Excel 2010
ABC
1OrigValueChangeValueDiff
2Fiesta 1.6 MK 7 (2008-2013)Fiesta 1.6 MK 7 B299 (2008-2013)B299
3Fiesta 1.6 MK 7 (2008-2013)Fiesta 1.6 MK 8 (2008-2013)8
Sheet1
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
I doubt you're gonna get a solution for this.
The logic behind it looks extremely complicated.
 
Upvote 0
If you are sure that there will only be one different area in each field it should be possible to come up with something, but if there may be multiple differences (e.g. field1="abc ghi lmn" and field2="abcdef ghi") then I agree with special-K99.
 
Upvote 0
If you are sure that there will only be one different area in each field it should be possible to come up with something, but if there may be multiple differences (e.g. field1="abc ghi lmn" and field2="abcdef ghi") then I agree with special-K99.

I do think its going to be something extra in column B if it helps. Row 3 is probably a bad example, I don't think it will be like that. Its just something that is extra in column B than to A.
 
Upvote 0
I'm wondering if you could get closer to the differences by substituting each word in cell B that occurs in cell A with a null ("") and doing the same for words in A that aren't in B. This would leave you with

things that are in A but not in B
things that are in B but not in A

(this is sounding a bit like Sesame Street!)

But how you would go about this with a formula I'm not sure. VBA would be a little tricky.
 
Upvote 0
It's strange because it seems a relatively easy thing compared to some well complicated macros I have had created for me! Basically its whats the difference between B and A!!!
 
Upvote 0
This is a quick function which relies on: the second parameter being longer than the first, and there is only one different area:

Place the code in a module in your vba, and insert a formula in column c2 as follows: =finddiff(a2,c2)
Then copy down. NB it will give a Value! error if the first field is less than 2 characters - if you want a more robust version I will have to do it tonight (after 7pm BST).

Code:
Function finddiff(a As String, b As String)
lendiff = Len(b) - Len(a)
If lendiff > 0 Then
For j = 1 To Len(b)
If Mid(a, j, 1) <> Mid(b, j, 1) Then Exit For
Next
' at this point the first difference is found

For k = Len(b) - 1 To j Step -1
If Mid(a, k - lendiff, 1) <> Mid(b, k, 1) Then Exit For
Next

finddiff = Mid(b, j, (k - j) + 1)
Else
finddiff = ""
End If

End Function
 
Last edited:
Upvote 0
It's strange because it seems a relatively easy thing compared to some well complicated macros I have had created for me! Basically its whats the difference between B and A!!!

It's only easy because you're comparing cell A and B by instantly looking at them and ignoring the similarities.
That's what a VBA/formula would also have to do but in a methodical way by looking at each word individualy and searching for it in the other cell.
 
Upvote 0
The following CSE array formula will work assuming:

value in column B always has an added string somewhere in it and the length difference between A and B is completely attributable to the added string (change 33 to bound max string length)

=MID(B2,MATCH(FALSE,MID(A2,ROW(1:33),1)=MID(B2,ROW(1:33),1),0),LEN(B2)-LEN(A2))

enter with ctrl-shift-enter, not just enter

if the strings are identical it will return #N/A

it will not work your example in row 3
 
Last edited:
Upvote 0
You'll want to make the row function argument absolute reference so it will drag down properly (and change 33 to bound you longest expected string):

=MID(B2,MATCH(FALSE,MID(A2,ROW($1:$33),1)=MID(B2,ROW($1:$33),1),0),LEN(B2)-LEN(A2))
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top