Excel converting HTML ASCII characters to readable characters

  • Thread starter Deleted member 6694
  • Start date
D

Deleted member 6694

Guest
I've got some partial HTML code within an Excel cell and it showing the special character codes rather than the readable characters. Here is an example I.

THIS IS A 12" RECORD FROM THE 80's


It should say THIS IS A 12" RECORD FROM THE 80's but the " and ' are showing as " and ' within the cell instead!

Here's the list of the codes and what characters they correspond to.


Is there any quick fix such as a function that allows you to use something like CHAR(34) which would convert and show the " and ' within the cell?

Worst case is I could just create a macro to do a find/replace for certain special characters but surely there's an easier way?

Basically I'm looking to format the fucker ;)
I've got some partial HTML code within an Excel cell and it showing the special character codes rather than the readable characters. Here is an example I've made up.

THIS IS A 12" RECORD FROM THE 80's

It should say THIS IS A 12" RECORD FROM THE 80's but the " and ' are showing as " and ' within the cell instead!

Here's the list of the codes and what characters they correspond to.


Is there any quick fix such as a function that allows you to use something like CHAR(34) which would convert and show the " and ' within the cell?

Worst case is I could just create a macro to do a find/replace for certain special characters but surely there's an easier way?

Basically I'm looking to format the fucker ;)
haha, stupid edit has quoted me :lol::lol:
 
Last edited by a moderator:


I take it converting the format of the cells to plain text and re-pasting the text in doesnt work?
Nah, I've created a huge macro for all the special characters to find and replace but it takes a while for some reason (it didn't in the spreadsheet I used to create and test it!)

Basically I'm needing to know how you convert HTML Special Characters to show as normal text within Excel.
 
Nah, I've created a huge macro for all the special characters to find and replace but it takes a while for some reason (it didn't in the spreadsheet I used to create and test it!)

Basically I'm needing to know how you convert HTML Special Characters to show as normal text within Excel.


How are you importing the text into excel? Is it as basic as using one of the special paste functions?
 
How are you importing the text into excel? Is it as basic as using one of the special paste functions?
I'm importing the HTML into Excel using a function. It splits it up into 100's of cells in 1 column. I did it this way so I can search for a specific line of HTML with the cell character limit kicking in.

From that cell I then do a search for part of the text within it. Sometimes it has various special characters that replace the likes of % & ' ; " etc etc. So that's why I needed convert the individual cell into a format that is easy to read.

If the text was in Cell A1, I was hoping it was something as simple as =HTMLDECODE(A1)

I'm after something that converts something like this daft example.

THIS IS A 12" RECORD FROM THE 80's/!©®

into this

THIS IS A 12" RECORD FROM THE 80's/!©®


Logon or register to see this image



The macro method I've created using FIND/REPLACE works but it's obviously doing 100's of them searching for any possible special character just for that 1 cell just in case there's one or more special characters in it. I only want it to replace the special characters in the specified cell also, not elsewhere in the file where the original code may be. I hope that makes it easier to understand.
 
Last edited by a moderator:
Nah, I've created a huge macro for all the special characters to find and replace but it takes a while for some reason (it didn't in the spreadsheet I used to create and test it!)

Basically I'm needing to know how you convert HTML Special Characters to show as normal text within Excel.
Have you typed the last sentence into google?

 
Last edited:
Have you typed the last sentence into google?

Aye and I've found a few various suggestions but I can't get it to work. I've seen that link but not sure what to do with it and don't you need to purchase the add-on to get that code to work? I see the code that is on that link changes the whole sheet, whereas I just want to change a specified cell and not affect anything else in the file.

I don't want to convert HTML formatting but HTML special characters as the tags aren't a problem as the info within the cell is from within a tag anyway. Here's part of the macro I've created but it takes a while to go through 100's of find/replace as this is just 5 of them. I've even turned off the autocalculations within the macro to see if it speeds up but it doesn't

Sub SPECIAL_CHARACTERS()
Sheets("Sheet1").Select
Range("A1").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="!", Replacement:="!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=""", Replacement:="""", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="#", Replacement:="#", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="$", Replacement:="$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
End Sub
 
Aye and I've found a few various suggestions but I can't get it to work. I've seen that link but not sure what to do with it and don't you need to purchase the add-on to get that code to work? I see the code that is on that link changes the whole sheet, whereas I just want to change a specified cell and not affect anything else in the file.

I don't want to convert HTML formatting but HTML special characters as the tags aren't a problem as the info within the cell is from within a tag anyway. Here's part of the macro I've created but it takes a while to go through 100's of find/replace as this is just 5 of them. I've even turned off the autocalculations within the macro to see if it speeds up but it doesn't

Sub SPECIAL_CHARACTERS()
Sheets("Sheet1").Select
Range("A1").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="!", Replacement:="!", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=""", Replacement:="""", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="#", Replacement:="#", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="$", Replacement:="$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
End Sub
 
Have you typed the last sentence into google?
I got that to work in a test file but it's changing every cell that gets pasted and will run all the time. I only want to change a specific cell on a single worksheet.

Same thing as it does all the worksheet and I only want to change a specific cell on a single worksheet. Do you know how to change the code in that link to get it to work on just one specified cell (D12 for example) rather than the whole worksheet?

I'm no VBA coder and I've learnt what I know through hacking other code but I can't suss the code in that link to not go through all the rows and columns but rather specify a single cell.
Finally sussed it with help from an Excel forum as I just put =DECODEHTML(D12) in whichever cell I want to show the decoded version :cool: Thanks anyway lads ;)

Function DECODEHTML(s As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\&\#\d{3}\;"
Set matches = .Execute(s)
For Each mtch In matches
s = Replace(s, mtch, Chr(Mid(mtch, 3, 3)))
Next mtch
End With
DECODEHTML = s
End Function


Another method that also works

Function DECODEHTML(txt As String) As String
Dim sp As Variant, i As Long
sp = Split(txt, "&")
For i = 0 To UBound(sp)
If Left(sp(i), 1) = "#" Then
sp(i) = Chr(Mid(sp(i), 2, InStr(1, sp(i), ";") - 2)) & Mid(sp(i), InStr(1, sp(i), ";") + 1)
End If
Next i
DECODEHTML= Join(sp, "")
End Function



 
Last edited by a moderator:
I got that to work in a test file but it's changing every cell that gets pasted and will run all the time. I only want to change a specific cell on a single worksheet.


Same thing as it does all the worksheet and I only want to change a specific cell on a single worksheet. Do you know how to change the code in that link to get it to work on just one specified cell (D12 for example) rather than the whole worksheet?

I'm no VBA coder and I've learnt what I know through hacking other code but I can't suss the code in that link to not go through all the rows and columns but rather specify a single cell.
Finally sussed it with help from an Excel forum as I just put =DECODEHTML(D12) in whichever cell I want to show the decoded version :cool: Thanks anyway lads ;)

Function DECODEHTML(s As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\&\#\d{3}\;"
Set matches = .Execute(s)
For Each mtch In matches
s = Replace(s, mtch, Chr(Mid(mtch, 3, 3)))
Next mtch
End With
DECODEHTML = s
End Function


Another method that also works

Function DECODEHTML(txt As String) As String
Dim sp As Variant, i As Long
sp = Split(txt, "&")
For i = 0 To UBound(sp)
If Left(sp(i), 1) = "#" Then
sp(i) = Chr(Mid(sp(i), 2, InStr(1, sp(i), ";") - 2)) & Mid(sp(i), InStr(1, sp(i), ";") + 1)
End If
Next i
DECODEHTML= Join(sp, "")
End Function



You could sell that to Microsoft.
 

Back
Top