Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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,772 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

572 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