Solved

Converting a cell with formatted text to HTML

Posted on 2014-03-26
16
256 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
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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

820 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