Help!!! Is it possible to only show what is different between two columns

[Tim326515465]

Is it possible to show only the difference between two columns and have what is different in another column.
example
column A_______Column B
123___________123-ce.abc
234___________a234.bcd
123___________103-a.cde

Then column C i would want it to say

Column C
-ce.abc
a.bcd
0-a.cde

 

[shrivallabha]

Welcome to Chandoo. Org forums.

You'd want to implement a formula like in cell C1:
=SUBSTITUTE(B1,A1,"",1)

But there will be couple of items to consider.
1. Will column B always have longer text as compared to column A?
2. Will you have data like
abc, abcabcabc
in column A and column B respectively? What will be expected result if answer is yes.

 

[Tim326515465]

Welcome to Chandoo. Org forums.

You'd want to implement a formula like in cell C1:
=SUBSTITUTE(B1,A1,"",1)

But there will be couple of items to consider.
1. Will column B always have longer text as compared to column A?
2. Will you have data like
abc, abcabcabc
in column A and column B respectively? What will be expected result if answer is yes.

1. Yes column B will always be longer.

2. 95% of the time column A will match the beginning of column B. Column A format will have a mixture of letters and numbers and special characters. Column B may have 1 or more characters at the end of column A (column A will have 123 and column B will have 123a) but will always have ".a2" or ".abc". Column B would look like 123a.a2 or 123a.abc or 123.a2 or 123.abc.


80% of the time the format of column A will be 123456, AB123456, 12-345678

 

[Tim326515465]

Thank you the above formula seems to work...I have a lot of information to go through