Solved

Converting a cell with formatted text to HTML

Posted on 2014-03-26
16
250 Views
Last Modified: 2014-03-26
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
0
Comment
Question by:Steve Wales
  • 9
  • 7
16 Comments
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
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
 
LVL 22

Author Comment

by:Steve Wales
Comment Utility
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
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
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
 
LVL 22

Author Comment

by:Steve Wales
Comment Utility
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
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
Comment Utility
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
 
LVL 22

Author Comment

by:Steve Wales
Comment Utility
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
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
That means it's not finding the function. Is it still in a module of the same workbook you have the formulas in?
0
 
LVL 22

Author Comment

by:Steve Wales
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:nutsch
Comment Utility
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
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
Also do you get the same error if you put back the original code?
0
 
LVL 22

Author Comment

by:Steve Wales
Comment Utility
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
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
What's in X2?
0
 
LVL 22

Author Comment

by:Steve Wales
Comment Utility
X2 Contents
0
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 500 total points
Comment Utility
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
 
LVL 22

Author Comment

by:Steve Wales
Comment Utility
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
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
Glad to help.

Thomas
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now