Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 28th 05, 10:19 PM
yellow281
 
Posts: n/a
Default 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
View this thread: http://www.excelforum.com/showthread...hreadid=391123


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


try
=SUBSTITUTE(B2,A2,"")


--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=391123

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


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
View this thread: http://www.excelforum.com/showthread...hreadid=391123

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


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
View this thread: http://www.excelforum.com/showthread...hreadid=391123

  #5   Report Post  
Old July 29th 05, 05:40 AM
Morrigan
 
Posts: n/a
Default


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
View this thread: http://www.excelforum.com/showthread...hreadid=391123



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


Once again genius..

Thank you for all your kind help!

Kate


--
yellow281
------------------------------------------------------------------------
yellow281's Profile: http://www.excelforum.com/member.php...o&userid=25695
View this thread: http://www.excelforum.com/showthread...hreadid=391123



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find the cell value in excel by using vb code Michael Excel Discussion (Misc queries) 5 June 14th 05 01:24 PM
How to refer to a cell format code? A difficult problem Excel Discussion (Misc queries) 1 May 25th 05 08:42 AM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
Finding the cell reference of a matching search value ndrewitt Excel Worksheet Functions 0 April 13th 05 01:43 AM
Make Change Case in Excel a format rather than formula Kevin Excel Worksheet Functions 1 March 18th 05 08:53 PM


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

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017