Help in function creation

[ADS_SDF]

Hello everyone I need to write a VBA function that performs the following tasks:

- enter "<p>" before each capital letter at the beginning of the sentence.
- enter "<strong>" before each capital letter within a sentence, and "</strong> at the end of that word.
- After each dot, type "</p>"

Can anyone help me?
Thank you so much!

 

[Legacy 143009]

Do you have single sentences in each cell? Or a whole paragraph in single cell?

 

[ADS_SDF]

I have the whole paragraph in each cell

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you confirm the exact required result if this was in a cell?
Today is Wednesday, December 21. Tomorrow will be Thursday! Is that you Ann-Maree?

or this?
What is on ABC today?

 

[ADS_SDF]

Hi Peter,
Let's imagine in A1 there is the following paragraph Today is Wednesday, December 21. Tomorrow will be Thursday! Is that you Ann-Maree?
The result of the function I would need is
<p>Today is <strong>Wednesday</strong>, <strong>December</strong>21. </p><p>Tomorrow will be <strong>Thrusday</strong>!</p><p>Is that you <strong>Ann-Maree</strong>?</p>

Thank you for helping!!

 

[Peter_SSs]

Ok, so the mid-sentence capital M in Maree does not get a <strong> before it.

And a letters followed by "-" do not get a </strong> between but letters followed by "." or "," or "!" or "?" do get a </strong> between. Could we have definitive lists of other non-letter characters that do/don't get the </strong> before them? Are any of these characters possible in your data and if so which category do they fit in?
@ # $ % ^ & * ( ) _ { } [ ] < > : ; " ' / | \
Could digits be involved in the text? If so, some examples of data and results?

To help confirm the rules, the result for my second example in post 4 would be?

 

[Legacy 143009]

Assuming your paragraph in cell A1:

Sub myFunction()
  Dim words As Variant
  Dim paragraph As String, word As String
  words = Split(Range("A1").Value, " ")
  
  
  paragraph = "<p>" & words(0)
  For i = 1 To UBound(words)
    If words(i) <> "" Then
      If UpperCaseCheck(words(i)) Then
        If Right(words(i - 1), 1) = "." Then
          paragraph = paragraph & "<p>" & words(i)
        ElseIf Right(words(i), 1) = "." Or Right(words(i), 1) = "," Then
        paragraph = paragraph & " <strong>" & Left(words(i), Len(words(i)) - 1) & "</strong>" & Right(words(i), 1)
        Else
          paragraph = paragraph & " <strong>" & words(i) & "</strong>"
        End If
      ElseIf Right(words(i), 1) = "." Then
        paragraph = paragraph & " " & words(i) & "</p>"
      Else
      paragraph = paragraph & " " & words(i)
    End If
    End If
  Next
  Range("A2").Value = paragraph & "</p>"
End Sub
Function UpperCaseCheck(ByRef word As Variant) As Boolean
    Dim intASCII As Integer
    intASCII = Asc(Left(word, 1))
    Select Case intASCII
        Case 65 To 90 'ASCII Code for A to Z
            UpperCaseCheck = True
        Case Else
            UpperCaseCheck = False
    End Select
End Function

 

[Legacy 143009]

Ok, so the mid-sentence capital M in Maree does not get a <strong> before it.

And a letters followed by "-" do not get a </strong> between but letters followed by "." or "," or "!" or "?" do get a </strong> between. Could we have definitive lists of other non-letter characters that do/don't get the </strong> before them? Are any of these characters possible in your data and if so which category do they fit in?
@ # $ % ^ & * ( ) _ { } [ ] < > : ; " ' / | \
Could digits be involved in the text? If so, some examples of data and results?

To help confirm the rules, the result for my second example in post 4 would be?

Good point. I haven't thought of that.

 

[ADS_SDF]

Thank you Flashbond!

It would be possible to add a condition according to which if two or more words in Capital letters are consecutives, separated by a space, comma, hyphen, "and", ' or "'s" the </strong> applies only to the last one

For Example:
New York--> <strong>New York</strong> (space)
Australia, America --> <strong>Australia, America</strong> (comma)
New York and Australia --> <strong> New York and Australia </strong> (and)
New-York --> <strong>New-York </strong> (hypen)
Rio’s Sugar Loaf Mountain --> <strong> Rio’s Sugar Loaf Mountain </strong> (single quote/ 's)

About the dot, the question mark and the exclamation mark they will be the at the end of the sentences, so they will be followed by </p>. The other non-letter characters do not apply to my data.
Hope to have answered to your question Peter!

 

[Legacy 143009]

It's tedious work. I am not that experienced in word processing. Maybe somebody else can help in advance.