SUBSTITUTING TEXT FROM A LIST

[FARAZ QURESHI]

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!

 

[Bernie Deitrick]

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

 

[L. Howard Kittle]

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

 

[Bernie Deitrick]

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]

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]

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]

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