# Help in function creation



## ADS_SDF (Dec 21, 2022)

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!


----------



## Flashbond (Dec 21, 2022)

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


----------



## ADS_SDF (Dec 21, 2022)

I have the whole paragraph in each cell


----------



## Peter_SSs (Dec 21, 2022)

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 (Dec 21, 2022)

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 (Dec 21, 2022)

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?


----------



## Flashbond (Dec 21, 2022)

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
```


----------



## Flashbond (Dec 21, 2022)

Peter_SSs said:


> 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?
> @ # $ % ^ & * ( ) _ { } [ ] < > : ; " ' / | \
> ...


Good point. I haven't thought of that.


----------



## ADS_SDF (Dec 21, 2022)

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!


----------



## Flashbond (Dec 21, 2022)

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


----------



## ADS_SDF (Dec 21, 2022)

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!


----------



## ADS_SDF (Dec 21, 2022)

Peter_SSs said:


> 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?
> @ # $ % ^ & * ( ) _ { } [ ] < > : ; " ' / | \
> ...


I forgot to answer to your second question. 

The output of the sentence *What is on ABC today? *should be
* <p>What is on <strong>ABC</strong> today?</p>*


----------



## Peter_SSs (Dec 21, 2022)

This sort of thing is notoriously difficult. A human can usually identify a sentence pretty easily but to tell Excel how to do that is not so easy. In a paragraph it would be easy to say that a break between sentences is identified by a "." followed by a space followed by an upper case letter, but ..
a. As demonstrated in my examples, a sentence can end with characters other then "."
b. A "." followed by a space followed by an upper case letter does not necessarily mean a sentence break anyway (eg "I like poetry by J*. B*lake and his brother.")

There is also some discrepancy in your example below


ADS_SDF said:


> 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>*


You have retained the space character before the first "*</p>*" but omitted it before the second "*</p>*"

I have assumed that it should be omitted. So you could give this UDF a try.


```
Function AddTags(para As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(^|[.?!] )(?=[A-Z])"
    AddTags = .Replace(para, "$1<p>")
    .Pattern = "([.?!] |$)"
    AddTags = .Replace(AddTags, "$1</p>")
    .Pattern = "( )(?=[A-Z])"
    AddTags = .Replace(AddTags, "$1<strong>")
    .Pattern = "(<strong>[^, .?!]+)([, .?!])"
    AddTags = Replace(.Replace(AddTags, "$1</strong>$2"), " </p", "</p")
  End With
End Function
```

ADS_SDF.xlsmAB1Today is Wednesday, December 21. Tomorrow will be Thursday! Is that you Ann-Maree?<p>Today is <strong>Wednesday</strong>, <strong>December</strong> 21.</p><p>Tomorrow will be <strong>Thursday</strong>!</p><p>Is that you <strong>Ann-Maree</strong>?</p>2What is on ABC today?<p>What is on <strong>ABC</strong> today?</p>Sheet1Cell FormulasRangeFormulaB1:B2B1=AddTags(A1)


----------

