Convert HTML to number

D

dth7018

I have found a table on the web that I would like to use in Excel. If I copy
and paste it into Excel as HTML, it fits into the celss just fine, but I
can't do any calculations on it. If I paste it as text (Unicode or not), it
all squishes into column A. How can I change the format of the cells so that
calculations can be performed? Doing the usual Cell Format stuff doesn't
work.

Thanks.
 
G

Gord Dibben

Simply formatting cells is not enough.

After re-formatting all to General, copy an empty cell, select the "numbers" and
Paste Special>Add>OK>Esc.

If there are no extraneous HTML characters like non-breaking spaces in the
cells, the numbers should calculate OK.

If not, you may have to do some stripping.

David McRitchie's TRIMALL macro is your best bet for this operation.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
K

Khan

hi!
may be this can help you out to remove HTML characters
but you will
have to do it at a time for one column,
1)select the column in which you have HTML characters then press Ctrl+C

to copy the column.
2) then open (windows) NOTEPAD press Ctrl+V to paste your data in
NOTPAD.
3) then press Ctrl+H to find and replace the HTML characters.
4) just select one of the HTML character normaly it should be in the
end of every line copy it then paste it as value of Find what:
5) don't put any thing in Replace with: value press replace all .
6) press Ctrl+A to copy all and paste it back in excel column.

Regards,
Salim
 
K

Khan

hi!
may be this can help you out to remove HTML characters
but you will
have to do it at a time for one column,
1)select the column in which you have HTML characters then press Ctrl+C


to copy the column.
2) then open (windows) NOTEPAD press Ctrl+V to paste your data in
NOTPAD.
3) then press Ctrl+H to find and replace the HTML characters.
4) just select one of the HTML character normaly it should be in the
end of every line copy it then paste it as value of Find what:
5) don't put any thing in Replace with: value press replace all .
6) press Ctrl+A to copy all and paste it back in excel column.


Regards,
Khan
 
K

Khan

hi!
may be this can help you out to remove HTML characters
but you will have to do it at a time for one column,
1)select the column in which you have HTML characters then press Ctrl+C

to copy the column.
2) then open (windows) NOTEPAD press Ctrl+V to paste your data in
NOTEPAD.
3) then press Ctrl+H to find and replace the HTML characters.
4) just select one of the HTML character normaly it should be in the
end of every line copy it then paste it as value of Find what:
5) don't put any thing in Replace with: value press replace all .
6) press Ctrl+A to copy all and paste it back in excel column.
Actully NOTEPAD can recognise those characters which excel can't.

Regards,
Salim
 
K

Khan

hi!
may be this can help you out to remove HTML characters
but you will have to do it at a time for one column,
1)select the column in which you have HTML characters then press Ctrl+C


to copy the column.
2) then open (windows) NOTEPAD press Ctrl+V to paste your data in
NOTEPAD.
3) then press Ctrl+H to find and replace the HTML characters.
4) just select one of the HTML character normaly it should be in the
end of every line copy it then paste it as value of Find what:
5) don't put any thing in Replace with: value press replace all .
6) press Ctrl+A to copy all and paste it back in excel column.
Actully NOTEPAD can recognise those characters which excel can't.


Regards,
Khan
 
Top