function that will in c1 take the sentence

[mgm90210M]

a	b	c	d	e	f	g
2						id
3	ronld com co if	ronld				co
4	com dan	dan				com
5	guy id mano roi	mano				roi
6	roni soares fri mito	soares fri mito				guy
7						roni

<tbody>
</tbody>

please i need in c3 function until c6 a function that
will in
c1 take the sentence in b3 and remove the word that in g colom ( g2 antil g7)

the word will remove please
and will include space instant of the word that remove

like in b6 remove only "roni" that include in g column


it is imported to Pay attention that when in g column have the word "com"

the function only delete "com"
and not "commmg"



thanks

 

[jasonb75]

To get you started,

=trim(substitute(SUBSTITUTE(" "&B3&" "," "&$G$2&" "," ")," "&$G$3&" "," "))

Note that this only covers G2 and G3, you will need to add the remaining 4 SUBSTITUTE functions into the formula for each of the remaining cells G4:G7

 

[mgm90210M]

To get you started,

=trim(substitute(SUBSTITUTE(" "&B3&" "," "&$G$2&" "," ")," "&$G$3&" "," "))

Note that this only covers G2 and G3, you will need to add the remaining 4 SUBSTITUTE functions into the formula for each of the remaining cells G4:G7

• please i don't know how to do that
  can you please help me
  and include the all function

 

[mgm90210M]

a	b	c	d	e	f	g
2						id
3	ronld com co if	ronld				co
4	com dan	dan				com
5	id mano roi	mano				roi
6	roni.soares fri mito.com	soares fri mito				.com
7						roni.

<tbody>
</tbody>

please can you look on the new table
with the remove of the .com and roni.


can you supply please the full function

 

[jasonb75]

Try and do it yourself, then next time maybe you will be able to do your own without asking.

I've done the next one for you and changed the text colour so that you can see it.

=trim(substitute(SUBSTITUTE(" "&B3&" "," "&$G$2&" "," ")," "&$G$3&" "," "))

=trim(substitute(substitute(SUBSTITUTE(" "&B3&" "," "&$G$2&" "," ")," "&$G$4&" "," ")," "&$G$3&" "," "))

 

[mgm90210M]

please

i need from g2 until g60
i canot do it my self
i dont know anything about the function building in exeal
i try to learn
but i don't understanding the function building process


please help me

 

[jasonb75]

First of all you added criteria, .com and roni. something that I didn't see earlier, but which is a significant enough variance from your original example to make the suggested solution fail.

In addition to this, I provided a solution based on the fact that the irregular layout of your example suggested only a small range, G2:G7, now you're changing that to G2:G60.

Given the changes, the solution I suggested is now neither practical or suitable, any other variations could further complicate the solution.

 

[mgm90210M]

so what you suggest as a solution to my problem

 

[jasonb75]

First add this code to the vba editor.

Option Explicit
Public Function mgmsubstitute(txt As String, rng As Range) As String
Dim cl As Range
    txt = " " & Replace(txt, ".", " ") & " "

        For Each cl In rng
            txt = Replace(txt, " " & cl & " ", " ")
        Next
    mgmsubstitute = Trim(txt)

End Function

Then add the formula to the sheet. =mgmsubstitute(B3,$G$2:$G$60) and fill down.