Converting a cell with formatted text to HTML

I have a spreadsheet that has a couple of columns with formatted text - mainly bolds and italics.

I'd like to be able to create another column that converts that to html so it can be extracted and displayed via a html viewer.

So if the existing data says:

Keyword: Do something quickly

I'd like the new cell to say

<b>Keyword:</b> Do <i>something</i> quickly

Is this possible ?  I've done some google searching and found examples where some clever VBA turns the entire spreadsheet / worksheet into an html document but I just want to be able to add a new column and then replicate all the contents of formatted column into the new column, converting the formatting to HTML.

Is this possible ?

Thanks
LVL 23
Steve WalesSenior Database AdministratorAsked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
Yes I can, one small change in the last line:

Function HTMLFormat(rgText As Range) As String
Dim lCharLoop As Long, sTemp As String
Dim bBold As Boolean, bItalic As Boolean, sTags As String

For lCharLoop = Len(rgText.Value) To 1 Step -1
    sTags = ""
    If InStr(rgText.Characters(lCharLoop, 1).Font.FontStyle, "Bold") > 0 Then
        If Not bBold Then
            bBold = True
            sTags = "</b>"
        End If
    Else
        If bBold Then
            bBold = False
            sTags = "<b>"
        End If
    End If
    
    If InStr(rgText.Characters(lCharLoop, 1).Font.FontStyle, "Italic") > 0 Then
        If Not bItalic Then
            bItalic = True
            sTags = sTags & "</i>"
        End If
    Else
        If bItalic Then
            bItalic = False
            sTags = sTags & "<i>"
        End If
    End If

    sTemp = Mid(rgText.Value, lCharLoop, 1) & sTags & sTemp

Next

HTMLFormat = IIf(bBold, "<b>", "") & IIf(bItalic, "<i>", "") & sTemp

End Function

Open in new window

0
 
nutschCommented:
You can use the attached UDF to do that. It will work for italics and bold only.

Function HTMLFormat(rgText As Range) As String
Dim lCharLoop As Long, sTemp As String
Dim bBold As Boolean, bItalic As Boolean, sTags As String

For lCharLoop = Len(rgText.Value) To 1 Step -1
    sTags = ""
    If InStr(rgText.Characters(lCharLoop, 1).Font.FontStyle, "Bold") > 0 Then
        If Not bBold Then
            bBold = True
            sTags = "</b>"
        End If
    Else
        If bBold Then
            bBold = False
            sTags = "<b>"
        End If
    End If
    
    If InStr(rgText.Characters(lCharLoop, 1).Font.FontStyle, "Italic") > 0 Then
        If Not bItalic Then
            bItalic = True
            sTags = sTags & "</i>"
        End If
    Else
        If bItalic Then
            bItalic = False
            sTags = sTags & "<i>"
        End If
    End If

    sTemp = Mid(rgText.Value, lCharLoop, 1) & sTags & sTemp

Next

HTMLFormat = sTemp

End Function

Open in new window

0
 
Steve WalesSenior Database AdministratorAuthor Commented:
Appreciate the code. That's awesome.

However, I know enough about Excel to be dangerous only.

I've never worked with VBA.

Any chance you can extend your answer to tell me what I need to do to run it and populate the new columns ?

I assume I do something like add the new column, then I can plug this macro in somehow and run it and it will process all rows and populate ?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
nutschCommented:
Open your workbook and go to the visual basic editor (Alt + F11). Insert \ Module from the insert menu,

Copy the code in said module.

Now in your worksheet, you can use the function HTMLFormat just like you would any other function, e.g.

=HTMLFormat(A2)

Then do a copy / paste values as required.

The code I've given you is for a user-defined function, so it will behave like a regular excel function, as long as the code is attached to the workbook.

Makes sense?

Thomas
0
 
Steve WalesSenior Database AdministratorAuthor Commented:
OK that code is awesome!

It doesn't quite work though.  

If the first character of the cell is bold, it doesn't put the <b> there.

Instead of
<b>Keyword:</b> Do <i>something</i> quickly

I get:
Keyword:</b> Do <i>something</i> quickly

Can you offer a solution for that ?
0
 
Steve WalesSenior Database AdministratorAuthor Commented:
Sorry, but copying and pasting that new macro in, now instead of all the text without the leading <b> (99% of the cells have leading bold attributes) - now it just shows #NAME?
0
 
nutschCommented:
That means it's not finding the function. Is it still in a module of the same workbook you have the formulas in?
0
 
Steve WalesSenior Database AdministratorAuthor Commented:
Yep - all I did was hit ALT-F11 and replace the code from earlier with the new code.

While typing =HTMLFormat it does prompt me for the function name so it seems to know it's there.

If I go back and replace the code in the module with the original code ... it now also gives me #NAME?

Weird.
0
 
nutschCommented:
What does the whole formula on your worksheet look like? Maybe it doesn't like the parameter you're sending?

If you can give me both how you wrote the formula, and the content of the cell that you're converting that returns the issue.

Is it a single cell or all of them?
0
 
nutschCommented:
Also do you get the same error if you put back the original code?
0
 
Steve WalesSenior Database AdministratorAuthor Commented:
Putting back the original formula is producing the same error.

My cell containing the call to the function looks like this:

=HTMLFormat(X2)

I even tried deleting the module and adding a new one again, no change.

If I take the modified code and insert it into a new spreadsheet, it works just fine.

I thought I'd try to add a new module (Module2) and copy in the code again and call it HTMLFormat2 just in case there was something funny with the first one.  It's doing the same thing.

As I'm typing "=HTMLFormat" it's even prompting me for both functions:

HTMLFormat function
0
 
nutschCommented:
What's in X2?
0
 
Steve WalesSenior Database AdministratorAuthor Commented:
X2 Contents
0
 
nutschConnect With a Mentor Commented:
Can you try closing the file and reopening it, making sure that you enable the macros?

If you're not asked to enabled the macros, go to File \ Excel Options \ Trust Center \ Macros to ensure that all macros are not disabled.
0
 
Steve WalesSenior Database AdministratorAuthor Commented:
And with that piece of sage advice you earned your points.  Thank you.  Looks like macros got disabled and as soon as I turned them back on, it all worked!
0
 
nutschCommented:
Glad to help.

Thomas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.