Hi All, this is my first post. I am using Excel 2010 and I enter a string into cell A1. I then want to search this cell from a list of values looking for a match and replace that with nothing "". ie: I enter w480-krusty-01 into Cell A1. I then want to search cell A1 from a list of values (w240-, w240_,b480-, b480_,w480-,w480_) to see if there is a match and in this instance there is a match so the result would be krusty-01 (the w480- has been replaced with nothing). THANKYOU for your help, Brian
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Excel 2010 search a cell from a list of values and partial replace with ""
- Thread starter bcain
- Start date
Welcome
Off the top of my head, what you want is this.
Cell A1, contains the full string,
Cell A2, contains the "thing" you want to replace.
Cell A3, contains the formula below.
=MID(A1;1;SEARCH(A2;A1)-1)&MID(A1;SEARCH(A2;A1)+LEN(A2);LEN(A1))
When you have replaced one value, you should copy cell A3, and paste it into A1 (VALUES ONLY)
then change the contents of A2, to replace next. (if the contents of cell A2 is not found, the formula will return !VALUE)
If you dont like the !VALUE, do this,.
=IFERROR(MID(A1;1;SEARCH(A2;A1)-1)&MID(A1;SEARCH(A2;A1)+LEN(A2);LEN(A1));"Not found")
Last edited:
Upvote
0
You can copy the text you want to search for into the formula if you want,
Replace every "A2" within the formula with "w240_", or "b480-" etc, you can make 6 different formulas, which will return the text, or "Not Found" when you paste the text.
If you type the text into the formula remember " " on each side
Replace every "A2" within the formula with "w240_", or "b480-" etc, you can make 6 different formulas, which will return the text, or "Not Found" when you paste the text.
If you type the text into the formula remember " " on each side
Upvote
0
You can copy the text you want to search for into the formula if you want,
Replace every "A2" within the formula with "w240_", or "b480-" etc, you can make 6 different formulas, which will return the text, or "Not Found" when you paste the text.
If you type the text into the formula remember " " on each side
HI Sturla, thank you for you quick reply. Yes I tried your formulae and Yes it worked. The problem I have is that there could be up to 20 different string values that I need to search for in Sheet1!A1. What I was hoping to find was a formulae where I could enter all the strings that I wish to search for in Sheet2!C1:C20. Then in Sheet1!A1 I would put the full string. In cell Sheet1!D1 I would enter your formulae which would check all the values in Sheet2!C1:C20 against the full string in Sheet1!A1 and if it found a match would remove the thing I want to replace with nothing "". In summary Sheet1!A1 contains the full string. Sheet2!C1:C20 contains the "things" I want to replace. Sheet1!D1 contains your formulae which is the result of the full string less the thing i want to replace.
Sheet1!A1 - w480-krusty-01
Sheet1!D1 - your formulae with the answer krusty-01
Sheet2!C1:C20 - w480_, w240-,b480-,w480-,...........
Regards,
Brian
Upvote
0
Oke, its a big one, but should do the trick
=IFERROR(MID(A1;1;SEARCH(INDEX(C1:C20;MATCH(A1;C1:C20;1));A1)-1)&MID(A1;SEARCH(INDEX(C1:C20;MATCH(A1;C1:C20;1));A1)+LEN(INDEX(C1:C20;MATCH(A1;C1:C20;1)));LEN(A1));"Not found")
I used Index-Match to lookup the value in C1:C20, I replaced A2, with the new formula: INDEX(C1:C20;MATCH(A1;C1:C20;1))
Upvote
0
Hi Sturla, I copied your formulae above to my excel spreedsheet, and pasted in Cell Sheet1!D2. I entered my search criteria in Cells Sheet1!C1:C20. It wasn't until I replaced the ; with , in your formulae that excel accepted it. The results from the formulae were "Not found". Because of the complexity of the formulae and my limited understanding of Excel 2010 I haven't been able to figure what is going wrong. Did this formulae work for you? Your help on why it is not returning krusty-01 instead of "Not found" would as always be greatly appreciated. Regards, Brian
Upvote
0
Abit unprecise on mypart.
yes, you are propably using US version of excel, which means you need "," and not ";".
And yes, it worked for me. the "Not Found" answer appears when there is an error, or rather it did not find anything in the range C1:C20 that matched anywhere in the string you placed in A1. You have the "things" you want to remove in column C yes? (I have set it up to column C sheet1, I think that is the problem here)
To test this, either place the "things" (I feel like i'm SOO precise here -.- ) in column C in sheet1, or add "Sheet2!" infront of the parts "C1:C20" within the formula.
Upvote
0
HI Sturla, I have tried what you have suggested but still getting "Not found" in the cells. My email address is getemailed@gmail.com. If you could email me your address I will forward you my workbook. If you could then post to this forum what I am doing wrong. Thanks again for your help, Brian
Upvote
0
I had a look, It was the matching criteria (MATCH) that did not recognize it, so I added a "MID" part to the function so that it would return the correct matching.
New formula,
=IFERROR(MID(A1;1;SEARCH(INDEX(Sheet2!$C$1:$C$20;MATCH(MID(A1;1;5);Sheet2!$C$1:$C$20;0));A1)-1)&MID(A1;SEARCH(INDEX(Sheet2!$C$1:$C$20;MATCH(MID(A1;1;5);Sheet2!$C$1:$C$20;0));A1)+LEN(INDEX(Sheet2!$C$1:$C$20;MATCH(MID(A1;1;5);Sheet2!$C$1:$C$20;0)));LEN(A1));"Not found")
The part in RED was added (3 places) so that it would match, I noticed all the things you wanted to remove were always infront of the strings in column A, and always 5 chars long. That made it an easy fix.
Upvote
0
Similar threads
