earcelor
asked on
How to convert text into Excel to HTML entities text specially special characters using a macro ?
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.
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.
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
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
ASKER
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...
Hope you could help me...
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.Cell
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
Sub HTMLEncode()
Dim rng As Range
Dim i As Integer
Dim strValue As String
For Each rng In ActiveSheet.UsedRange.Cell
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
ASKER
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 "a".
What is the way to modify your code in order it just changes special characters and not normal one s?
Thanx
Example:
The "a" becomes "a".
What is the way to modify your code in order it just changes special characters and not normal one s?
Thanx
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
Paulo
(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 :)
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 :)
ASKER
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 : "Ž" on the mac
and gives "é" on a pc which seems to be the good value.
What can i do in order it works on both systems ?
If I run the macro on a mac i got strange chars and not same code as on a windows plateform.
example:
"é" gives : "Ž" on the mac
and gives "é" on a pc which seems to be the good value.
What can i do in order it works on both systems ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 : "Ž" on the mac
and gives "é" on a pc which seems to be the good value.
What can i do in order it works on both systems ?
If I run the macro on a mac i got strange chars and not same code as on a windows plateform.
example:
"é" gives : "Ž" on the mac
and gives "é" on a pc which seems to be the good value.
What can i do in order it works on both systems ?
ASKER
This works with AscW function !!!
Thanx a lot.
P.
Thanx a lot.
P.
Ir really was a shot in the dark :)
Glad it worked out. Thanks for the points :)
Glad it worked out. Thanks for the points :)
ASKER
and
Träger