Excel formula

[cos2a]

Hello!
I hope you can help me, i appreciate alot.
I have a text description, that i have to write it with html tags.

Is there a formula in excel to change all the upper letters from this text like: SET, SIDED, DOUBLE, SPECIFICATION, etc" With this html tag before and after "<strong> </strong>" ?
Thanks for help!
<p>SET - colored markers make it easy to mark important information, but usually the available colors run out quickly. This set includes as many as 40 pieces of highlighters, which will surely meet the expectations of the most demanding people.<br />DOUBLE-SIDED - they are very practical two-sided highlighters. WITH on one side they have a round head, which will be perfect for writing or drawing, on the other side they have an oblique head, which is perfect for highlighting important information.<br />VERY EFFICIENT CARTRIDGE - these high-performance pens have a special refill that it is enough even for 300 m , so they will surely serve you for a long time in perfect condition.<br />CASE INCLUDED - colorful markers are packed in a very practical case secured with a lock. It makes it easier to keep your highlighters tidy and you can keep them close at hand without the risk of losing half of them .<br />SPECIFICATION - number of pieces: 40 pcs; two-sided: yes; case included: yes; slanted head: yes; round head: yes; cartridge efficient for 300 m: yes; dimensions: 16/16 / 9.5cm; weight of the set: 0.895 kg<br
/>SPECIFICATION:</p>
<p>number of pieces: 40 pcs<br />two-sided: yes<br />case included: yes<br />slanted head: yes<br />round head: yes<br />efficient cartridge for 300 m: yes<br />dimensions: 16/16 / 9.5cm<br />weight of the set: 0.8950 kg</p>

Thanks!

 

[Micron]

You could do this with code if you are ok with that option, or wait for someone who is formula savvy to chime in. Unfortunately I never got around to learning Regular Expressions within vba (not sure if that is even possible for what you want) so I'm usually stuck with looping in code. This code takes one second to run and changes most of the requirements. Unfortunately, words like INCLUDED are not included unless you add the code tags like in >INCLUDED. You would have to modify the array values to suit your needs. However, I think I'd be getting the bold and lower case values from 2 columns in a sheet instead. Easier to manage. This is a function in case you want to pass cell values as the html paragraphs.

Function ReplaceUcase()
Dim str As String
Dim ary() As String
Dim i As Integer

ary = Split("SET,set,SIDED,sided,DOUBLE-SIDED,double-sided,SPECIFICATION,specification," _
& "VERY,very,EFFICIENT,efficient,CARTRIDGE,cartridge,CASE,case,INCLUDED,included", ",")

'No idea where your html is located so I just put it into a string variable for demo purposes
str = "<p>SET - colored markers make it easy to mark important information, but usually the available colors run out quickly. " _
& "This set includes as many as 40 pieces of highlighters, which will surely meet the expectations of the most demanding people." _
& "<br />DOUBLE-SIDED - they are very practical two-sided highlighters. WITH on one side they have a round head, which will " _
& "be perfect for writing or drawing, on the other side they have an oblique head, which is perfect for highlighting important information." _
& "<br />VERY EFFICIENT CARTRIDGE - these high-performance pens have a special refill that it is enough even for 300 m , so they " _
& "will surely serve you for a long time in perfect condition.<br />CASE INCLUDED - colorful markers are packed in a very practical case " _
& "secured with a lock. It makes it easier to keep your highlighters tidy and you can keep them close at hand without the risk of " _
& "losing half of them .<br />SPECIFICATION - number of pieces: 40 pcs; two-sided: yes; case included: yes; slanted head: yes; " _
& "round head: yes; cartridge efficient for 300 m: yes; dimensions: 16/16 / 9.5cm; weight of the set: 0.895 kg<br" _
& "/>SPECIFICATION:</p><p>number of pieces: 40 pcs<br />two-sided: yes<br />case included: yes<br />slanted head: yes<br />" _
& "round head: yes<br />efficient cartridge for 300 m: yes<br />dimensions: 16/16 / 9.5cm<br />weight of the set: 0.8950 kg</p>"

For i = 0 To UBound(ary)
     str = Replace(str, ary(i), ary(i + 1))
     i = i + 2
Next

'Debug.Print str
ReplaceUcase= str

End Function

BTW, I suggest you run your html through a checker. For one thing, <br> is an empty tag. It does not require a closing tag.

 

[cos2a]

Thanks alot for your reply!
The only thing is that i have thousand descriptions like this, and the upper letters are always different. I use a html editor for html tags, but i have to bold all the upper letters manually, one by one, so i tought there is a way to do it bulk for all of them.

 

[Micron]

The only way 2 ways I can think of to get around the problem I described is
1- to run the procedure (working off of a list instead) and for those that get missed, add the missed set of characters to the list and run again. You'll approach 100% results each time you add a list value but may never actually get there. Nothing will happen to the words that were already corrected.

2- loop over every letter in a string and if it finds 2 upper case in a row, keep looking until it finds a character that is not UCase, then lower case that set. That might be slow and quite bit more code I'd say. However, if there happens to be 2 or more UCase characters that you want to keep you will have to manually put them back as they were.

Perhaps someone will help you with a simpler approach, but that's all I can think of. Good luck!

 

[6StringJazzer]

BTW, I suggest you run your html through a checker. For one thing, <br> is an empty tag. It does not require a closing tag.

There are no closing tags for <br> in the code above. Since XML was introduced the <br> tag is commonly written as <br />