Solved

Converting a cell with formatted text to HTML

Posted on 2014-03-26
16
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39956869
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
ID: 39957051
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
ID: 39957081
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Author Comment

by:Steve Wales
ID: 39957129
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
ID: 39957154
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
ID: 39957174
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
ID: 39957188
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
ID: 39957192
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
 
LVL 39

Expert Comment

by:nutsch
ID: 39957200
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
ID: 39957210
Also do you get the same error if you put back the original code?
0
 
LVL 22

Author Comment

by:Steve Wales
ID: 39957274
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
ID: 39957284
What's in X2?
0
 
LVL 22

Author Comment

by:Steve Wales
ID: 39957305
X2 Contents
0
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 500 total points
ID: 39957321
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
ID: 39957346
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
ID: 39957413
Glad to help.

Thomas
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 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