Mass Replace words from strings

[onp]

Hello, I have found this sample that I have attached here that seems to do what I am looking for, but I am unable to change the Array nor copy and paste down.
How is that done?

In column A is the original data. I need to be able to change/replace words in column A all the way down to about 5K rows

In Column B are the words I want to remove,
in column C I would input replacement words, or leave it Blank if no none are required
In column D would be the Final output as wished.

How can I change the current array to perform the task for all the data in column A?

In the current status it only works for the first 9 Rows in column A and can only find whats written in the first 3 Rows of Column B.

This is the code in the module of the attachment

Thank you for your input
OP

[ORoos]

Hi there,

No need to change anything in the code. You need to set the ranges/arrays when entering the formula first.

Try the steps below:

Clear all formulas in column D.

In cell 'D2' enter the formula

Please Login or Register  to view this content.

Orange = the array where your original data is. So 6,000 should cover your 'about 5k.
Blue = Array where the strings are you want to remove.
Green = Array with the replacement strings.

Blue and Green arrays should be the same length. The sample above is to row 20, so 19 strings. Change the figure as required.

Once you have the correct range/arrays you need, pull down the formula (double click in the right-bottom corner of the field to 'auto-fill' to the last row.

Let us know if that works.

[sintek]

In Column B are the words I want to remove,
in column C I would input replacement words, or leave it Blank if no none are required
In column D would be the Final output as wished.

A different option...

Please Login or Register  to view this content.

[onp]

Hi Sintek, Thank you very much, your code works wonderful magic and FAst.
I just don't understand how to change the code to refer to different columns. Could you please let me know what I need to change in the code?
for instance:
Change you your code to a revised column positions:

In column E is the source data
In Column H are the words I want to remove,
in column G I would input replacement words, or leave it Blank if no none are required
In column K would be the Final output as wished.

It would help me understand how to change columns in the code in the event I need to modify my template.

Thank you Sintek
Op

[onp]

Hi Oroos, Unfortunately I am unable to make the formula work. Its not removing the words when i copy down the formula. The VBA code by Sintek works great, I just need to learn how to designate different columns.

Thank you ORoos
cheers
Op

[sintek]

Change you your code to a revised column positions:

This I will have to see...All depends on whether or not there are headings, blank columns etc...
Please upload a small sample of actual file setup...

[kvsrinivasamurthy]

Here is the code for UDF

Please Login or Register  to view this content.

[Glenn Kennedy]

onp.

I asked you twice in another recent thread to confirm that you're still using Excel 2019. You joined quite a while ago, so your profile may be out of date.

Kindly confirm what you ARE using and update your profile (if required)... and don't continue to ignore me.

[onp]

Hi Glenn, Just checked and updated profile. Apologies if I inadvertently ignored your question. I do normally answer all questions
Cheers
Op

[onp]

Hi kvsrinivasamurthy, thank you for your response.

I have tested the code on all the data I need to replace words with, but not working quite well.
Basically it is replacing just a few of the words listed in column B "Find What? Eventually there will be more words in that "Replace what" List.

However, if I add an additional column (E) and replace words from the already replaced words result in column D
and add Column (F) to replace words from Column E all the words in the list will eventually be replaced. - See new Sample.

This makes me add too many columns in order to replace all the listed words.
As I review the data to verify the results I realize that there are other issues that I will need to resolve
such as duplicate words (A26) within the string. So not sure if the remove duplicate could be implemented to the UDF.

Thank you so much for your help
Cheers
OP

[sintek]

Hi Sintek, Thank you very much, your code works wonderful magic and FAst.

And...response to Post 6

[onp]

Hi SIntek I am preparing the Actual file for you as I type. Will be posted in a few min. Hope it will not give you a headache

[sintek]

Have "loadshedding" for the next 2 - 4 hours so can only check in again later...

[onp]

Here it is Sintek.

Only row 55 has actual formulas. Just to keep file size Small. You can copy down if needed.
Column BM to CJ is where I filter the words from The actual Statement Data (Pasted in Column E)
with various and way too many formulas.

Such, beside Confusing me when I have to trace a problem in a formula result and the computer Smoking when the rows reach well above 5000,
And especially when real-time data that is constantly updating (Witch it needs to be) Is A nightmare.

So the idea was rather than using all of the formulas to find the needed Numbers and or words,
I thought by Replacing and/or removing words/symbols/numbers/duplicates within the string From Column E and Placing the new filtered string into a new column,
such would allow me to really get rid of lots of formulas and headaches and hence make things faster.
But because of lack of commitment in where the column and range of the filter data will end up, I will need to change such definitions accordingly.

Hope its not confusing
Cheers Sintek and a big thank you
OP

P.S. If you Get #Value and #N/A thats where the formula is trying to retrieve the real-time data. In my case Thinkorswimm platform.

[kvsrinivasamurthy]

UDF code. This will remove all the words in "Find what" Range (Column B).

Please Login or Register  to view this content.

[onp]

I don't know Ans...But its looking rel good don't want to get exited yet. Still testing it on all of my data. Will report back soon

[sintek]

Hope its not confusing

Has no bearing whatsoever to your post 4 requirement...

[onp]

YES!!! Ans, it works fantastically, cant thank you enough for your help it really makes my hair pulling come to an end
Thank you so much Ans

Cheers
All the best
OP

[onp]

Just wanted to thank all for your time and the help you have provided. kvsrinivasamurthy found the solution I was looking for. I shall mark the thread as Solved.
I love this Forum
CHeers to all
OP