|
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 02:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com