Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Find the cell value in excel by using vb code  Excel Discussion (Misc queries)  
How to refer to a cell format code?  Excel Discussion (Misc queries)  
Copy cell format to cell on another worksht and update automatical  Excel Worksheet Functions  
Finding the cell reference of a matching search value  Excel Worksheet Functions  
Make Change Case in Excel a format rather than formula  Excel Worksheet Functions 