Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 553
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Substituting values TLC Excel Discussion (Misc queries) 1 October 22nd 07 07:38 PM
Substituting for cell Dave Excel Discussion (Misc queries) 3 August 24th 07 11:31 PM
Substituting letters for numers Mike New Users to Excel 4 May 8th 07 04:41 AM
Replacing or Substituting Text Naya Excel Worksheet Functions 1 September 22nd 05 02:55 AM
substituting two text occurances in same cell. Hassan Alameh Excel Worksheet Functions 4 March 9th 05 02:50 PM


All times are GMT +1. The time now is 02:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"