?
Solved

How to convert text into Excel to HTML entities text specially special characters using a macro ?

Posted on 2003-03-12
12
Medium Priority
?
1,596 Views
Last Modified: 2008-01-16
I have an excel sheet containing text in several languages with several special characters for each language.
I'd like to get a macro into excel which could convert the texts containing special characters into HTML entities:

example:

"Inégal" should become "Inégal"
"Träger" should become "Träger "

Please someone could tell me how to process and where to find a macro which could do the job ?

Thanx a lot.

0
Comment
Question by:earcelor
[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
  • 6
  • 6
12 Comments
 

Author Comment

by:earcelor
ID: 8120346
Inégal

and

Träger
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8120372
seems like you have the same problem here at EE :)

I would suggest you recorded a macro with yourself replacing one of those strange characters by their equivalent. And then add the code to handle all the other chars. You can post the initial code here for us to help you change it if necessary.

Another alternative I've used sometimes in the web would be to identify the ASCII code ranges that need to be replaced and create a generic function (where you don't have to specify all chars one by one).

Which one do you prefer?

Paulo
0
 

Author Comment

by:earcelor
ID: 8120397
Thanx Polo, I'd prefer a generic solution because i have a lot of languages to manage and don't know all the special characters i could encountered in all those languages.

Hope you could help me...
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 9

Expert Comment

by:pauloaguia
ID: 8122730
Sorry for the delayed answer but I had to go digging up some old code to get this one:

Sub HTMLEncode()
        Dim rng As Range
        Dim i As Integer
        Dim strValue As String

        For Each rng In ActiveSheet.UsedRange.Cells
            If Not rng.HasFormula Then
                strValue = ""
                For i = 1 To Len(rng.Value)
                    If isWebOK(Mid(rng.Value, i, 1)) Then
                        strValue = strValue & Mid(rng.Value, i, 1)
                    Else
                        strValue = strValue & "&#" & Format(Asc(Mid(rng.Value, i, 1)), "000") & ";"
                    End If
                Next
                rng.Value = strValue
            End If
        Next
End Sub

Function isWebOK(str As String)
    isWebOK = (Asc(str) >= 32 And Asc(str) <= 123)
End Function

Basically all chars whose ascii code isn't between 32 and 123 will be converted to the HTML code. Don't forget to copy the sheet to a new one and run the code on the copy since you'll lose the original text.

Hope this helps

PAulo
0
 

Author Comment

by:earcelor
ID: 8126085
Thank you it seems to very effective but with your method all the characters are replaced by html entities and not only the special characters ones.

Example:

The "a" becomes "&#097;".

What is the way to modify your code in order it just changes special characters and not normal one s?

Thanx
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8126465
That's odd. I didn't get it replaced. The ascii value of 'a' is 97 which falls within the WebOk range determined by that last function. what code page are you using? Are you sure it's an 'a' and not an 'á' or 'ã' or something?

Paulo
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8126471
(damn it. EE translated my chars... as I believed it happened to yours.

I meant are you sure it's not an _a_ or a _a'_ or an _a~_ ? (assume the puctuation this is only one char).

By the way what was the char you typed? It probably got lost in translation as well :)
0
 

Author Comment

by:earcelor
ID: 8126542
Ok it's working but there is again a strange thing on it.

If I run the macro on  a mac i got strange chars and not same code as on a windows plateform.

example:

"é" gives : "&#142;" on the mac

and gives "&#233;" on a pc which seems to be the good value.

What can i do in order it works on both systems ?
0
 
LVL 9

Accepted Solution

by:
pauloaguia earned 100 total points
ID: 8126588
maybe the ascii codes are different in mac and PC? I didn't know that... I'll have to do some digging around but it will have to wait till I get home (in about 10 hours from now, I'm currently at the office).
Or maybe the Mac uses Unicode (I really don't know how a Mac works so I'm kind in the dark on this one). If so then you could try to use the AscW function instead of Asc. Not sure if it will help though.

I'll be back.

Paulo
0
 

Author Comment

by:earcelor
ID: 8126625
Ok it's working but there is again a strange thing on it.

If I run the macro on  a mac i got strange chars and not same code as on a windows plateform.

example:

"é" gives : "&#142;" on the mac

and gives "&#233;" on a pc which seems to be the good value.

What can i do in order it works on both systems ?
0
 

Author Comment

by:earcelor
ID: 8126657
This works with AscW function !!!

Thanx a lot.

P.
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8126668
Ir really was a shot in the dark :)
Glad it worked out. Thanks for the points :)
0

Featured Post

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!

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

765 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