How to alter data on HTML webpage into Excell cells as numbers?

[roameri]

A saved HTML webpage is opened with Excel 2003. The HTML webpage has columns
of numerical data which go into Excel cells. If we operate on the Excell
cells, the contents of the cells do not behave like numbers. This is so also
after formatting the cells as numbers. However on the same spreadsheet the
other blank cells are properly reformatted as numbers.

[Karthik]

Simple...
When a cell is formated as text and has numaric data in it, changing
the format to number or currency will not work. Even thought the format
in the Format cell pop up box appears to be in number or currency
format.

There are many more ways to convert them to number and some of them are
:

1. Multiply/ divide all these cells by 1 in another Cell or add/
subtract 0.
2. Block the entire column and then do a data>Text to column.. > Click
finish (without going through all the steps).

3. Later versions of Excel show a green tag indicating a number is
stored as text and helps you convert all such cells into number format
in one go.


Thanks
Karthik Bhat

[goseespam@lot]

Hi Karthik,

Can you give us the steps to do this for a whole workbook?
And will this process clear away all the HTML tags and junk that comes
in with internet material?

Thanks,

Arthur

**********************************

On 29 Jul 2005 05:33:13 -0700, "Karthik" <[email protected]> wrote:
>There are many more ways to convert them to number and some of them are
>:
>
>1. Multiply/ divide all these cells by 1 in another Cell or add/
>subtract 0.

[David McRitchie]

to convert one worksheet you would select all cells,
and then run the TRIMALL macro which you will find
at http://www.mvps.org/dmcritchie/excel/join.htm#trimall
You will also see instruction there pointing you to
http://www.mvps.org/dmcritchie/exce/getstarted.htm
if you are not familiar with installing and using macros.

There is no way that you would want to attempt to do that using
additional columns for each existing column so that you can use
worksheet functions without using a macro.

If you want to expand that to do an entire workbook you will
have to select each sheet and run the macro. I'll have to
warn you that if you have data that looks like a date it will
become a date, it will be the same as if everything had a
format of General and you retyped what you see without the
spaces at beginning and end.

You could mess things up.
So make sure you test on a copy and check your results
carefully, because you usually look at the columns you want to
convert and convert them rather than indiscriminately processing
all cells in the used range of each worksheet. [thank you spell checker]

if you look on my sheets.htm page you will find a subroutine
to process all sheets, which you can modify to something like this.

Place the following in your personal.xls in the same module
that you have TrimALL

Sub TrimALL_for_all_Sheets()
'D.McRitchie, 2005-07-30, excel.newusers
Dim sht As Worksheet, RC As Long, rng As Range
RC = MsgBox("Are you sure you want to run TrimALL " _
& "on an Entire Workbook", vbYesNo)
If RC <> vbYes Then
MsgBox "Thank you, your data will not be touched, by your command"
Exit Sub
End If
For Each sht In Sheets
Sheets(sht.Name).Select
Set rng = Selection
Cells.Select
TrimALL
rng.Select
Next sht
End Sub

Remember test on a copy of your workbook.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<goseespam@lot> wrote in message news:[email protected]...
> Hi Karthik,
>
> Can you give us the steps to do this for a whole workbook?
> And will this process clear away all the HTML tags and junk that comes
> in with internet material?
>
> Thanks,
>
> Arthur
>
> **********************************
>
> On 29 Jul 2005 05:33:13 -0700, "Karthik" <[email protected]> wrote:
> >There are many more ways to convert them to number and some of them are
> >:
> >
> >1. Multiply/ divide all these cells by 1 in another Cell or add/
> >subtract 0.
>

[David McRitchie]

There was another part to your question.

I really have no idea what round tripping code would be in Excel
having obtained the content from HTML and that HTML from
who knows where. Since I can't see it, I can't tell you.
I can see the mess of HTML created from Excel, and even
worse as in Chip Pearson's list of shortcuts where data
came from Excel was pasted into Front Page and then
made into HTML you can see the file sizes on my xl2html.htm page.
http://www.mvps.org/dmcritchie/excel/xl2html.htm
but there's not much you can do about it if you go that route.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

[goseespam@lot]

Hi Dave,

Thank you for your detailed answer. Also some good material on your
website.
>>Remember test on a copy of your workbook.
As you say, its always a good idea to work on a copy of the original
db to avoid losing important data.

********************************************

On Sat, 30 Jul 2005 21:50:40 -0400, "David McRitchie"
<[email protected]> wrote:

>to convert one worksheet you would select all cells,
>and then run the TRIMALL macro which you will find
>at http://www.mvps.org/dmcritchie/excel/join.htm#trimall
>You will also see instruction there pointing you to
> http://www.mvps.org/dmcritchie/exce/getstarted.htm
>if you are not familiar with installing and using macros.
>
>There is no way that you would want to attempt to do that using
>additional columns for each existing column so that you can use
>worksheet functions without using a macro.
>
>If you want to expand that to do an entire workbook you will
>have to select each sheet and run the macro. I'll have to
>warn you that if you have data that looks like a date it will
>become a date, it will be the same as if everything had a
>format of General and you retyped what you see without the
>spaces at beginning and end.
>
>You could mess things up.
>So make sure you test on a copy and check your results
>carefully, because you usually look at the columns you want to
>convert and convert them rather than indiscriminately processing
>all cells in the used range of each worksheet. [thank you spell checker]
>
>if you look on my sheets.htm page you will find a subroutine
>to process all sheets, which you can modify to something like this.
>
>Place the following in your personal.xls in the same module
>that you have TrimALL
>
>Sub TrimALL_for_all_Sheets()
> 'D.McRitchie, 2005-07-30, excel.newusers
> Dim sht As Worksheet, RC As Long, rng As Range
> RC = MsgBox("Are you sure you want to run TrimALL " _
> & "on an Entire Workbook", vbYesNo)
> If RC <> vbYes Then
> MsgBox "Thank you, your data will not be touched, by your command"
> Exit Sub
> End If
> For Each sht In Sheets
> Sheets(sht.Name).Select
> Set rng = Selection
> Cells.Select
> TrimALL
> rng.Select
> Next sht
>End Sub
>
>Remember test on a copy of your workbook.
>---
>HTH,
>David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
>My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
><goseespam@lot> wrote in message news:[email protected]...
>> Hi Karthik,
>>
>> Can you give us the steps to do this for a whole workbook?
>> And will this process clear away all the HTML tags and junk that comes
>> in with internet material?
>>
>> Thanks,
>>
>> Arthur
>>
>> **********************************
>>
>> On 29 Jul 2005 05:33:13 -0700, "Karthik" <[email protected]> wrote:
>> >There are many more ways to convert them to number and some of them are
>> >:
>> >
>> >1. Multiply/ divide all these cells by 1 in another Cell or add/
>> >subtract 0.
>>
>
>

[David McRitchie]

You're welcome, I'm certainly glad I put that InputBox in there
as I often test by assigning a macro to one of four that have
reserved for testing. I incorrectly choose that button later
instead of the one that runs a macro using the name of the
macro from a cell. That wouldn't have been nice.

I don't often warn about using a copy of a workbook, but when the attack
would be on the entire workbook that goes beyond a normal test.
Actually I did test on a separate workbook but still had the
toolbar button assigned.

<goseespam@lot> wrote in ...
> Hi Dave,
>
> Thank you for your detailed answer. Also some good material on your
> website.
> >>Remember test on a copy of your workbook.
> As you say, its always a good idea to work on a copy of the original
> db to avoid losing important data.