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

Pquigrafamos

New Member
Joined
Sep 8, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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 :)
 

Attachments

  • Excel example1.PNG
    Excel example1.PNG
    10.5 KB · Views: 13

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,601
Office Version
  1. 365
Platform
  1. Windows
@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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,836
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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. o_O
 

Pquigrafamos

New Member
Joined
Sep 8, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,601
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,141,294
Messages
5,705,537
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top