CODEWITHSUNDEEP
vbaexcel
LenardG
LenardG

Reputation: 51

Macro to convert bold text in Excel cell to strong tags

I need to convert text in an Excel worksheet. If the cell contains bold text in the middle of the cell, I need to add HTML <strong> tags around it. I need this because I want to insert the text into a database.

I received several Excel sheets, all of which contain lots of translations in diffrent languages. Although I have worked with sheets like this to generate SQL statements, this time it is different because of the formatting that needs to be preserved.

I usually just transform the text and then concatenate into SQL statements. But this time I also need a macro/function to add <strong> tags to the text: I do not want to do it by hand, because if the original texts change, I would manually need to redo the modifications.

Upvotes: 2

Views: 1423

Answers (1)

LenardG
LenardG

Reputation: 51

Answer

I added a new Macro function to the worksheet. I created a module for this, and added the function into the module. This allowed me to call it using the familiar function notation of Excel inside the middle of a formula.

  1. Open the VBA Editor (Alt + F11 on Windows / Fn + Option + F11 on a Mac)
  2. If you do not yet have a Module, right-click VBAProject
  3. Select Insert >> Module
  4. Add code to the module

The following function is what I came up with:

Function AddStrongTags(ByVal aCell As Range) As String        
    Dim result As String
    Dim isBold As Boolean

    isBold = False

    For i = 1 To Len(aCell.Value)
        If aCell.Characters(i, 1).Font.FontStyle = "Bold" Then
            If isBold = False Then
                result = result + "<strong>"
                isBold = True
            End If
        Else
            If isBold = True Then
                result = result + "</strong>"
                isBold = False
            End If
        End If
        result = result + aCell.Characters(i, 1).Text
    Next i

    If isBold = True Then
        result = result + "</strong>"
    End If

    AddStrongTags = result
End Function

To run the function on cell A2 for example, I would type into the desired cell:

=AddStrongTags(A2)

Which will produce the text without the bold formatting but with the <strong> tags added.

The function returns a string, so it can be used to concatenate into my SQL statements. For example here is a simplified example of using the new function to create an SQL statement, which I would enter into an empty cell:

="INSERT INTO Foo ( lang, text ) VALUES ( 'en-GB', '" & AddStrongTags(A2) & "' )"

Upvotes: 3

Related Questions