Special cut, paste and replace

Hi Guys,

I have worksheet with data distributed over 4 columns, and over 25000 rows.

Data are grouped and each group is separated with one empty row from the other.

ColC has the core data, where certain words are formatted in red colour in each row.

What is needed is that each red string regardless of its length in each row is to be cut, pasted in ColE in the same row, and replaced with ten dots in ColC.

Attached is a made up example to illustrate the case. Sheet1 with data before replacing red strings, and Sheet2 after replacement with ColE added strings.

Highly appreciating any valuable assistance.

Many thanks.
T.

Dear terryhenderson

Try this
If I helped you press a button * AddRep

Hi Pan314

Please Login or Register  to view this content.

Hi Pan314,

Thank you very much for your time and effort spent on helping with my problem.
Just wondering if you have run your code on my sample file for testing. As I ran the code, I found:
1. it doesn't default to ColC to work on.
2. it works for one row at a time and does not loop to the next, ie I have to run it manually for every single row.
Is there any chance to have this fixed, please?

With my best regards,
T.

Dear terryhenderson

I wrote earlier that the code should be included in a cycle. This is now done. I hope that you can use. Try this


If I helped you press a button * AddRep

Hi Pan314

Please Login or Register  to view this content.

Dear Pan314,

Once again, thank you very much for your contribution to solve my problem.
The code is working, but extremely slow. It took 3 minutes to finish 42 rows. How long do you think it would take to finish over 25000 rows?
I hope there'll be some way to enhance the code performance, so this task is done quickly and accurately.

With my best regards,

T.

Another:

Please Login or Register  to view this content.

Hi John H. Davis,

Such a rocket code. It does the job, but instead of replacing red text with 10 dots, it counts the red letters and replaces them accordingly. Is it possible to fix the replacing dots to ten dots only?

Very much appreciated.

T.

Try:

Please Login or Register  to view this content.

Thank you very much John for such marvelous code, it ran very smoothly and did the job as exactly desired.
My deep thanking and gratitude.
All the best.
T.

You're welcome. Glad to help out and thanks for the feedback.

Hi JOHN H. DAVIS,

As we are running your code on different texts and even different languages, such as Arabic and Korean, we found:
1. for English short strings, absolutely no problem and performance is optimum.
2. for English, Arabic and Korean strings longer than 255 chars the code halts for a debug
3. for Arabic and Korean short strings, performance speed dramatically goes down for calculation.

Any precious suggestions?

With my best regards.
T.

Hi Guys,

As I proceeded with my question regarding this very topic of "Special cut, paste and replace" I received two masterpieces of code by both respected experts Pan314 and John H. Davis. Both scripts worked excellently on our English files.

The script performance speed was far much faster for that of John H. Davis', which made us prefer using it on large files of data, without any devaluation of Pan314 script.

On the course of work, I encountered a file in Arabic that needed to be processed as exactly as that in English. When I ran John's code, it halted for a debug to define character class, whilst running Pan314's script it actually did the job perfectly, but after a significant period of time.

I am putting both scripts in an attached example to the general community of the forum so that another look into both scripts may "unlock the genie". The attached file has two sheets, where Sheet1 has the Arabic text cited from the Holy Book, and the second as a backup for that text just in case.

That's it.

My thanking and greetings in advance.

T.

Hi Terry:

See if this modification of Pan314's code helps any? I not familiar with Arabic, so I can't be of much help trying to determine why mine is erroring out.

Please Login or Register  to view this content.

Hi John,

Thank you very much for such quick response. As I ran the script, it halted with a run-time error 1004 "unable to set the text property of the Character class", and it stopped at:

Please Login or Register  to view this content.

Any suggestions?

My best regards,
T.

I modified Pan314's code because you said it didn't error in Post#12. Looks like the same error as you cited in Post #12 for my. When I tested it, I didn't receive an error and it ran through and did something. However, since I don't know Arabic, I couldn't tell if it did what it was supposed too do. Try this one then:

Please Login or Register  to view this content.

Marvelous, the code ran smoothly without any halts. It is much faster than its first version, but still slower than expected and I think it returns to the nature of the structure of Arabic text which, I think, there's no much for us to do about it.

I truly believe that both of you John and Pan314 have done more than expected with this problem, which is solved from this point on.

Thank you very much for every minute you spent and effort you exerted in solving this problem.

My best personal regards.

T.