+ Reply to Thread
Results 1 to 7 of 7

Convert HTML to number

  1. #1
    dth7018
    Guest

    Convert HTML to number

    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.

  2. #2
    excelent
    Guest

    RE: Convert HTML to number

    what cind of data ?
    numbers, text or both?


    "dth7018" skrev:

    > 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.


  3. #3
    Gord Dibben
    Guest

    Re: Convert HTML to number

    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

    On Tue, 18 Jul 2006 13:11:01 -0700, dth7018 <[email protected]>
    wrote:

    >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.



  4. #4
    Khan
    Guest

    Re: Convert HTML to number

    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

    Gord Dibben wrote:
    > 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
    >
    > On Tue, 18 Jul 2006 13:11:01 -0700, dth7018 <[email protected]>
    > wrote:
    >
    > >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.



  5. #5
    Khan
    Guest

    Re: Convert HTML to number

    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



    Gord Dibben wrote:
    > 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
    >
    > On Tue, 18 Jul 2006 13:11:01 -0700, dth7018 <[email protected]>
    > wrote:
    >
    > >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.



  6. #6
    Khan
    Guest

    Re: Convert HTML to number

    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

    Gord Dibben wrote:
    > 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
    >
    > On Tue, 18 Jul 2006 13:11:01 -0700, dth7018 <[email protected]>
    > wrote:
    >
    > >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.



  7. #7
    Khan
    Guest

    Re: Convert HTML to number

    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
    Gord Dibben wrote:
    > 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
    >
    > On Tue, 18 Jul 2006 13:11:01 -0700, dth7018 <[email protected]>
    > wrote:
    >
    > >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.



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1