Comparing character strings in separate cells

[Odin]

Hi All,

I have asked here before and on other fora for help on the following
problem ( i am a total novice at VB and Macros):

I have a large excel spreadsheet with alpha-numeric data. I want to be
able to compare two cells in the same row and return the disimilar
characters in another cell(s).

e.g. I have in cell B2, "BB26" and in cell C2, "BBZ5". I need to
compare the cell C2 to cell B2 and return the disimilar characters in
cell D2 which in this case would be the characters "Z5".

If possible i would like to specify in cell E2 the positions of the
differing characters i.e. cell E2, "34" representing the 3rd and 4th
positions of the disimilar characters.

If there is a better and more elegant solution to this problem that
would provide a similar solution, i would appreciate your help very
much.

Thank you in advance.

 

[NickHK]

Are all the compared strings the same lengths ?
If not, what would you expect from "BB26" and "BBZ526" ?

How about if you just returned a binary string, representing each character
?
e.g. "BB26" and "BBZ5" returns "1100"
But this depend somewhat on your answer to the first question.

NickHK

 

[Odin]

Hi NickHK, the compared strings could be of different lengths. As i
already have columns with the string lengths, in your example I would
expect D2 to still return "Z5" in positions "34".

The binary string solution to cell E2 is interesting but in your
example would that return "110011" or just "1100"?

Odin

 

[Guest]

Based on the data you have provided, 26 could just as well be the disimilar
characters since at this point we don't know whether there is a constant
pattern to the data we are comparing, whether we are comparing one cell to a
range of cells or whether we are comparing cell for cell to the adjacent
column of cells. Perhaps you could state your problem a little clearer.

 

[NickHK]

The return value would depend on whether you are basing it on the 1st or
second string.
What value would you expect for "BBZ526" and "BB26" then ?

You could maybe use some "padding" character in the return string, so it is
as long as the longer string.

NickHK

 

[Odin]

Hi NickHK/JLGWhiz,

I hope that this example will enable you to understand the problem
better.

B C D E
1 RX5TGE RX5TGEU U 7
2 I3816AE 13816AE 1 1
3 A269LYT B269LYT M 1
4 LGP5XGB LGR5ZGB RZ 35
5 BB26 BBZ526 Z5 34
6 BBZ526 BB26 This does not ocur

Thanks again for the consideration.

 

[NickHK]

I think you will need to return something like a string or array of values
indicating whether each char in the second string is :
1-Right-char, right location
2-Right-char, wrong location
3-Wrong-char
4-Position does not exist in first string

What would you expect from
BB26 BBBB26

You need to define more clearly the rules of comparison required.

NickHK