Remember Me?

#1
July 28th 05, 10:19 PM
 yellow281 Posts: n/a
Removing a matching code from another cell

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
------------------------------------------------------------------------
yellow281's Profile: http://www.excelforum.com/member.php...o&userid=25695

#2
July 28th 05, 10:31 PM
 Ruthki Posts: n/a

try
=SUBSTITUTE(B2,A2,"")

--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503

#3
July 28th 05, 10:31 PM
 Morrigan Posts: n/a

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.

yellow281 Wrote:
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!

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094

#4
July 29th 05, 12:37 AM
 yellow281 Posts: n/a

Morrigan Wrote:
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!

--
yellow281
------------------------------------------------------------------------
yellow281's Profile: http://www.excelforum.com/member.php...o&userid=25695

#5
July 29th 05, 05:40 AM
 Morrigan Posts: n/a

Try this

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

yellow281 Wrote:
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
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094

#6
July 29th 05, 02:07 PM
 yellow281 Posts: n/a

Once again genius..

Thank you for all your kind help!

Kate

--
yellow281
------------------------------------------------------------------------
yellow281's Profile: http://www.excelforum.com/member.php...o&userid=25695

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Michael Excel Discussion (Misc queries) 5 June 14th 05 01:24 PM A difficult problem Excel Discussion (Misc queries) 1 May 25th 05 08:42 AM kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM ndrewitt Excel Worksheet Functions 0 April 13th 05 01:43 AM Kevin Excel Worksheet Functions 1 March 18th 05 08:53 PM

All times are GMT +1. The time now is 11:34 AM.