Formula be replaced with macro

[K0st4din]

Hello,
can anyone tell me and help whether this kind of formula can be replaced by macro and perform the same function. This is just an example, then I will change my columns.
The idea is to search in one column (chosen by me) and the string of text, if I find search terms to bring them back again for each row in another column.
For example, looking at column A2:A and returns in column B2:B

=REPLACE(REPT("+AF266";COUNTIF(A2;"*AF266*"))&REPT("+AF267";COUNTIF(A2;"*AF267*"))&REPT("+AF268";COUNTIF(A2;"*AF268*"))&REPT("+AF269";COUNTIF(A2;"*AF269*"))&REPT("+AF311";COUNTIF(A2;"*AF311*"))&REPT("+CF706";COUNTIF(A2;"*CF706*"))&REPT("+CF707";COUNTIF(A2;"*CF707*"))&REPT("+CF708";COUNTIF(A2;"*CF708*"))&REPT("+CF512";COUNTIF(A2;"*CF512*"))&REPT("+CF508";COUNTIF(A2;"*CF508*"))&REPT("+CF437";COUNTIF(A2;"*CF437*"))&REPT("+CF648";COUNTIF(A2;"*CF648*"))&REPT("+CF649";COUNTIF(A2;"*CF649*"))&REPT("+CF444";COUNTIF(A2;"*CF444*"))&REPT("+HF095";COUNTIF(A2;"*HF095*"))&REPT("+NF520";COUNTIF(A2;"*NF520*"))&REPT("+NF521";COUNTIF(A2;"*NF521*"))&REPT("+NF522";COUNTIF(A2;"*NF522*"))&REPT("+NF523";COUNTIF(A2;"*NF523*"))&REPT("+AF386";COUNTIF(A2;"*AF386*"));1;1;"")

Thanks in advance.

 

[K0st4din]

I again ask you, because my inquiries has gone far behind.

 

[Rick Rothstein]

Hello
I would like to ask you one more addition - in this formula that I have done, can be done, so if looking for a word to show me a code or another word. This macro can I do so that I can ask a condition like this: If you find this or that word, show me these letters?
Example:
peach, apricot, banana 4 pcs - Pea+Apr+Ban 4
But the idea is to be written and all the action happens in the macro automatically.
Thank you very much for your cooperation on your part.

I do not understand how the layout for the text shown in red above relates to the layout for your original data... nor do I understand how the layout or the results you want (shown in green above) relates to how the results were shown in your original request. Is this an entirely different question? My suggest would be to install one of the add-ins shown in my signature line below and then use it to make a snapshot of a sample of your data and a snapshot of the results you want from that data and copy/paste them into a response message here so we can see exactly what your data looks like and see exactly how you want the results presented.

 

[K0st4din]

The second macro that did work perfectly for me, ie I choose my column in which to search for a column in which to export search terms - which is just great. The question is how to do so in the very macro to ask him according to my word carry the desired contraction of the words?
In the attachment I have given an example of a few words and their sample cuts. If there is anything that is not clear I will specify.
Thank you once again.

 

[Rick Rothstein]

The second macro that did work perfectly for me, ie I choose my column in which to search for a column in which to export search terms - which is just great. The question is how to do so in the very macro to ask him according to my word carry the desired contraction of the words?
In the attachment I have given an example of a few words and their sample cuts. If there is anything that is not clear I will specify.
Thank you once again.

I am having trouble downloading thing from the internet (my copy of Windows needed to be reinstalled and I have been putting it off), so I cannot retrieve your file. If you wanted to send it to me directly, my email address is...

rick DOT news AT verizon DOT net

Make sure to include this thread's title in your email so I can relate what you send me back to this message thread.

 

[K0st4din]

If I wrote it correctly, you should have received it.

 

[Rick Rothstein]

If I wrote it correctly, you should have received it.

I received it.... now I have to think about how to do it. I probably won't be able to do that tonight (it is 12:45 AM here now), so I'll look at it in the morning after I get up.

 

[K0st4din]

Ok, no problem.
Thanks in advance.
I wish you all good.

 

[Rick Rothstein]

I received it.... now I have to think about how to do it. I probably won't be able to do that tonight (it is 12:45 AM here now), so I'll look at it in the morning after I get up.

Okay, I'm back. In order to do what you want, you will need to set up a "table" of your substitutions. I assumed in my code below that the words will be listed in Column D starting at Row 1 and the replacement text will be listed in Column E on a row-for-row basis. I also assumed your text is in Column A starting at Row 1. This is the macro I came up with...

Sub DoReplacements()
  Dim X As Long, Cell As Range, CellText As String, Words As Variant, Replacements As Variant
  Application.Volatile
  Words = Range("D1", Cells(Rows.Count, "D").End(xlUp))
  Replacements = Range("E1", Cells(Rows.Count, "E").End(xlUp))
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    CellText = ""
    For X = 1 To UBound(Words)
      If InStr(1, Cell.Value, Words(X, 1), vbTextCompare) Then CellText = CellText & "+" & Replacements(X, 1)
    Next
    Cell.Offset(, 1).Value = Mid(CellText, 2)
  Next
End Sub

 

[K0st4din]

Sir, you are God.
That's exactly what I needed. I do not know how to express my thanksgiving.
Always helps both of us need.
I'll have more questions, but will be published as other posts.
Be alive and well.
Once again thank you heartily

 

[K0st4din]

Dear Rick Rothstein,
can you help me a little more because it's starting to straighten the original file gets pretty big for the reason that at any sheet repeat columns for comparison and file becomes very large.
Can the macro to stay with the same idea, but the comparison to be made bearing information in only one sheet to all others?
Thank you very much sir.