Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUBSTITUTING TEXT FROM A LIST
I have a list of cells containing text to be removed from a collection. What
sort a formula would help to achieve an array matter like: =SUBSTITUTE(A1,B1:B15,"") with items in B1:B15 to be removed from text in A1? Please help urgently. Thanx! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUBSTITUTING TEXT FROM A LIST
Faraz,
You would need to use VBA to write a custom function: =MySUB(A1,B1:B15,"") Function MySub(Str1 As String, R1 As Range, Str2 As String) As String Dim myC As Range MySub = Str1 For Each myC In R1 MySub = Replace(MySub, myC.Value, Str2) Next myC MySub = Application.WorksheetFunction.Trim(MySub) End Function HTH, Bernie MS Excel MVP "FARAZ QURESHI" wrote in message ... I have a list of cells containing text to be removed from a collection. What sort a formula would help to achieve an array matter like: =SUBSTITUTE(A1,B1:B15,"") with items in B1:B15 to be removed from text in A1? Please help urgently. Thanx! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUBSTITUTING TEXT FROM A LIST
Try this, however if Bernie says you need a VBA solution, I may not
understand the question. He is a pro of course. In C1 enter this and pull down. =SUBSTITUTE(B1:B15,$A$1,"") HTH Regards, Howard "FARAZ QURESHI" wrote in message ... I have a list of cells containing text to be removed from a collection. What sort a formula would help to achieve an array matter like: =SUBSTITUTE(A1,B1:B15,"") with items in B1:B15 to be removed from text in A1? Please help urgently. Thanx! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUBSTITUTING TEXT FROM A LIST
Howard,
I interpreted the original question as "I have the script to a George Carlin skit in one cell, and want to remove all seven of the words you cannot say on TV using one formula" Not a pro, but an MVP ;-) Bernie "L. Howard Kittle" wrote in message ... Try this, however if Bernie says you need a VBA solution, I may not understand the question. He is a pro of course. In C1 enter this and pull down. =SUBSTITUTE(B1:B15,$A$1,"") HTH Regards, Howard "FARAZ QURESHI" wrote in message ... I have a list of cells containing text to be removed from a collection. What sort a formula would help to achieve an array matter like: =SUBSTITUTE(A1,B1:B15,"") with items in B1:B15 to be removed from text in A1? Please help urgently. Thanx! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUBSTITUTING TEXT FROM A LIST
Hey Bernie,
Two things if I may. Was my solution pertinent to solve the question..? And was I offensive to call you a "pro" instead of an MVP? Regards, Howard "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Howard, I interpreted the original question as "I have the script to a George Carlin skit in one cell, and want to remove all seven of the words you cannot say on TV using one formula" Not a pro, but an MVP ;-) Bernie "L. Howard Kittle" wrote in message ... Try this, however if Bernie says you need a VBA solution, I may not understand the question. He is a pro of course. In C1 enter this and pull down. =SUBSTITUTE(B1:B15,$A$1,"") HTH Regards, Howard "FARAZ QURESHI" wrote in message ... I have a list of cells containing text to be removed from a collection. What sort a formula would help to achieve an array matter like: =SUBSTITUTE(A1,B1:B15,"") with items in B1:B15 to be removed from text in A1? Please help urgently. Thanx! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUBSTITUTING TEXT FROM A LIST
Howard,
Your solution is the reverse of my solution - it removes the value in A1 from each of the strings in B1:B15 in turn. But only the OP knows which was actually required. And, no, I'm not offended by being called a pro... it's just that MVPs only help out as volunteers, whatever their individual motivation may be. Bernie MS Excel MVP "L. Howard Kittle" wrote in message ... Hey Bernie, Two things if I may. Was my solution pertinent to solve the question..? And was I offensive to call you a "pro" instead of an MVP? Regards, Howard "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Howard, I interpreted the original question as "I have the script to a George Carlin skit in one cell, and want to remove all seven of the words you cannot say on TV using one formula" Not a pro, but an MVP ;-) Bernie "L. Howard Kittle" wrote in message ... Try this, however if Bernie says you need a VBA solution, I may not understand the question. He is a pro of course. In C1 enter this and pull down. =SUBSTITUTE(B1:B15,$A$1,"") HTH Regards, Howard "FARAZ QURESHI" wrote in message ... I have a list of cells containing text to be removed from a collection. What sort a formula would help to achieve an array matter like: =SUBSTITUTE(A1,B1:B15,"") with items in B1:B15 to be removed from text in A1? Please help urgently. Thanx! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUBSTITUTING TEXT FROM A LIST
Well that makes sense, and I am pretty sure I now understand the "Pro" vs.
MVP. I'm a lurker and you are a HUGE contributor is kinda where I was coming from. Your stuff a poster can take to the bank, mine is an attempt to imitate the many MVP's out there. I get it right often and have fun doing so. Probably the larger compliment is the patience with which you MVP's show when helping out the posters and lurkers like me on getting our heads around a solution. Example... Peo sent me a 4 page e-mail detailing a complex VLOOKUP. I still drag it out and study it from time to time. Thanks, Bernie Regards, Howard "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Howard, Your solution is the reverse of my solution - it removes the value in A1 from each of the strings in B1:B15 in turn. But only the OP knows which was actually required. And, no, I'm not offended by being called a pro... it's just that MVPs only help out as volunteers, whatever their individual motivation may be. Bernie MS Excel MVP "L. Howard Kittle" wrote in message ... Hey Bernie, Two things if I may. Was my solution pertinent to solve the question..? And was I offensive to call you a "pro" instead of an MVP? Regards, Howard "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Howard, I interpreted the original question as "I have the script to a George Carlin skit in one cell, and want to remove all seven of the words you cannot say on TV using one formula" Not a pro, but an MVP ;-) Bernie "L. Howard Kittle" wrote in message ... Try this, however if Bernie says you need a VBA solution, I may not understand the question. He is a pro of course. In C1 enter this and pull down. =SUBSTITUTE(B1:B15,$A$1,"") HTH Regards, Howard "FARAZ QURESHI" wrote in message ... I have a list of cells containing text to be removed from a collection. What sort a formula would help to achieve an array matter like: =SUBSTITUTE(A1,B1:B15,"") with items in B1:B15 to be removed from text in A1? Please help urgently. Thanx! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Substituting values | Excel Discussion (Misc queries) | |||
Substituting for cell | Excel Discussion (Misc queries) | |||
Substituting letters for numers | New Users to Excel | |||
Replacing or Substituting Text | Excel Worksheet Functions | |||
substituting two text occurances in same cell. | Excel Worksheet Functions |