Removing a matching code from another cell

[yellow281]

Hi Guys!

My first post and I would like to say thanks in advance for any help I receive. I have researched this problem but cannot find a solution!

Okay, I have two columns.. Column A has a single code in each cell. Column B has several codes in each cell of which one of those codes matches Column A. What I would like to do is figure out how to have Column B look at Column A and say okay I dont need that matching code in my cell so let me remove it!

For example

Column A

1234
3456
4567
2345

Column B

2345 1234 9875 0987 2343
1234 3456 9875 4903 3459
4567 3456 0987 2345 1323
2345 0987 9887 9876 9000

I need to remove the codes from column B that match the code in the corresponding cell in column A.

I hope that makes sense... any ideas?

Thanks!

[Ruthki]

try
=SUBSTITUTE(B2,A2,"")

[Morrigan]

Try this

=CONCATENATE(LEFT(B1,FIND(A1,B1,1)-1),MID(B1,FIND(A1,B1,1)+LEN(A1)+1,LEN(B1)-FIND(A1,B1,1)+LEN(A1)))


Hope it helps.

Hi Guys!

My first post and I would like to say thanks in advance for any help I receive. I have researched this problem but cannot find a solution!

Okay, I have two columns.. Column A has a single code in each cell. Column B has several codes in each cell of which one of those codes matches Column A. What I would like to do is figure out how to have Column B look at Column A and say okay I dont need that matching code in my cell so let me remove it!

For example

Column A

1234
3456
4567
2345

Column B

2345 1234 9875 0987 2343
1234 3456 9875 4903 3459
4567 3456 0987 2345 1323
2345 0987 9887 9876 9000

I need to remove the codes from column B that match the code in the corresponding cell in column A.

I hope that makes sense... any ideas?

Thanks!

[yellow281]

Try this

=CONCATENATE(LEFT(B1,FIND(A1,B1,1)-1),MID(B1,FIND(A1,B1,1)+LEN(A1)+1,LEN(B1)-FIND(A1,B1,1)+LEN(A1)))


Hope it helps.

Can I say genius..

Thank you, it worked brilliantly... I cant quite there was a formula for this!

May I challenge you again? My B column has up to 12 sets of codes each seperated by a space in each cell. Is there a way I can tell the B column to select the first 4 codes to retain them then delete the rest? And if theres 4 codes or less to just leave the cell intact?

For example..

Before

B Column

1234 3456 2356 8788 5555 6789 0978 6342 1224 3244

After

B Column

1234 3456 2356 8788

Thank you!

[Morrigan]

Try this

=IF(LEN(B1)>19,LEFT(B1,19),B1)

Can I say genius..

Thank you, it worked brilliantly... I cant quite there was a formula for this!

May I challenge you again? My B column has up to 12 sets of codes each seperated by a space in each cell. Is there a way I can tell the B column to select the first 4 codes to retain them then delete the rest? And if theres 4 codes or less to just leave the cell intact?

For example..

Before

B Column

1234 3456 2356 8788 5555 6789 0978 6342 1224 3244

After

B Column

1234 3456 2356 8788

Thank you!

[yellow281]

Once again genius..

Thank you for all your kind help!

Kate