Solved

Converting a cell with formatted text to HTML

Posted on 2014-03-26
16
257 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
Office 365 Training for IT Pros

Learn how to provision Office 365 tenants, synchronize your on-premise Active Directory, and implement Single Sign-On.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

740 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