VBA: Formating specific text sections of a cell based on conditions.

[Pquigrafamos]

Hello, first post here

I have the following problem:

-I have extensive lists of sentences in Excel, in which there are words ending and beginning with <strong></strong>.
-Every day new words appear having these <strong></strong> remarks.
-There is the need of creating a macro to automatize this process.

An example of such cells:

<strong>Frog</strong> and also the <strong>dog</strong>

House animals such as the <strong>dog</strong>

Is appliable for the <strong>dog</strong>

Eating <strong>Bananas</strong>, and others

I am trying to find a way of making the words inside these marks (<strong></strong>) bold and in UPPERCASE.
A solution was found, which separates the text and concatenates it back as pretended. But this solution is not ideal, it is very heavy and it takes too much time to complete the task.
As such, the objective would be to set conditions that would format solely the words inside these remarks, making them bold and UPPERCASE, leaving the rest of the text as it was.

Hose animals such as the<strong>dog</strong> -> House animals such as the DOG

Does anyone know if this is possible?
Best regards

 

[Peter_SSs]

@bobsan42
Thanks for the additional information. It looks like @Pquigrafamos has revisited the thread since your suggested modification, but no specific comments about whether that solved the problem. I guess that the 'Like' might indicate that it did.

The main driver of the (relative) speed of my code is that as much as possible of the string modifications (replacing the <strong> tags, making the 'strong' word into upper case & replacing the 'Euro' characters) is done in memory and not on the worksheet. Then just the bolding has to be done individually on the worksheet at the end.

 

[bobsan42]

The main driver of the (relative) speed of my code is that as much as possible of the string modifications (replacing the <strong> tags, making the 'strong' word into upper case & replacing the 'Euro' characters) is done in memory and not on the worksheet. Then just the bolding has to be done individually on the worksheet at the end.

I gathered as much. I revisited my code and changed it to reduce the "physical" operations. Despite the significant speed improvement I only caught a glimpse of your tailights in the distance.

 

[Peter_SSs]

I only caught a glimpse of your tailights in the distance.

? I like that expression, but it sounds very "English-speaker-like" so it was a little surprising given ..

Me being a non-native English speaker

 

[bobsan42]

? I like that expression, but it sounds very "English-speaker-like" so it was a little surprising given ..

I had a lot of practice. And some native friends.

 

[Pquigrafamos]

er Strong_v2 worked for me but I had to modify it slig

View attachment 46586 Display language for non-unicode programs

I maybe wrong of course, but it is my understanding that while Excel and VBA support Unicode, the VB editor does not (or not too well at least). Me being a non-native English speaker made me deal with many issues on the subject. All I'm saying is one must be quite carefull when dealing with Unicode directrly in VBE. You can see below the change in the pasted characters in a code window and the immediate window. When used like that the sub does not find these characters. For example now I can produce Cyrillic characters in VBE, but others are simply shown as their cyrillic equivalents. However, copying them from the code window back into my post actually returns them as the orginally copied characters, which is a bit odd, I think - it would mean the original character codes are kept/preserved, just the symbols are displayed incorrectly. But then the sub does not replace them as it should, which means VBA reads the characters as displayed in the code window.

View attachment 46587
However, reading the string from the worksheet works w/o problems.
And you code, Peter, is really quite fast. I plan on spending some time studying, just have to find some

@Bobsan

@bobsan42
Thanks for the additional information. It looks like @Pquigrafamos has revisited the thread since your suggested modification, but no specific comments about whether that solved the problem. I guess that the 'Like' might indicate that it did.

The main driver of the (relative) speed of my code is that as much as possible of the string modifications (replacing the <strong> tags, making the 'strong' word into upper case & replacing the 'Euro' characters) is done in memory and not on the worksheet. Then just the bolding has to be done individually on the worksheet at the end.

Sorry, only now had the chance to reply! @bobsan42 's suggestion of assigning the EuroStr to a cell worked!
Now everything is working more than perfect and fast, I really appreciate a lot all the help here given to me.
Although I am not yet able to understand everything written in the different codes (hope to be able in the future), it is extremely interesting to analyze the different approaches suggested.

A very big thank you!! :D

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.