I've copy/pasted the web page array of the details of my phone calls
from my on-line verizon cell phone bill into an Excel worksheet. The
number shown for the minutes of duration of each call doesn't equate
to an Excel number and the SUM() function reports zero. What's a line
of code (or other process) that will convert these numeric-appearing
entries into actual numbers? The text in the editing bar is two
spaces followed by a cipher, e.g. " 1" for one minute, so that the
cipher appears just to the right of center.

Neither of the following work. They both convert to the number zero
instead of to the actual number of minutes.

ActiveCell.Value = Val(LTrim(ActiveCell.Value))
ActiveCell.Value = LTrim(ActiveCell.Value)

Using ActiveCell.Text for the right-hand expression also doesn't seem
to work.

The cell formatting in the column has been converted to number, zero
decimal places.

Thanks for any help.

Fred Holmes

These cells in the worksheet are really strange. If I insert another
column alongside the original column and use the N() formula to
extract the number, e.g. N(G12) is the formula in H12, it reports zero
in all instances. ???

On Wed, 27 Apr 2005 10:55:37 -0400, Fred Holmes wrote:

I've copy/pasted the web page array of the details of my phone calls
from my on-line verizon cell phone bill into an Excel worksheet. The
number shown for the minutes of duration of each call doesn't equate
to an Excel number and the SUM() function reports zero. What's a line
of code (or other process) that will convert these numeric-appearing
entries into actual numbers? The text in the editing bar is two
spaces followed by a cipher, e.g. " 1" for one minute, so that the
cipher appears just to the right of center.

Neither of the following work. They both convert to the number zero
instead of to the actual number of minutes.

ActiveCell.Value = Val(LTrim(ActiveCell.Value))
ActiveCell.Value = LTrim(ActiveCell.Value)

Using ActiveCell.Text for the right-hand expression also doesn't seem
to work.

The cell formatting in the column has been converted to number, zero
decimal places.

Thanks for any help.

Fred Holmes

Fred,

Try this as well

With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fred Holmes" wrote in message
...
I've copy/pasted the web page array of the details of my phone calls
from my on-line verizon cell phone bill into an Excel worksheet. The
number shown for the minutes of duration of each call doesn't equate
to an Excel number and the SUM() function reports zero. What's a line
of code (or other process) that will convert these numeric-appearing
entries into actual numbers? The text in the editing bar is two
spaces followed by a cipher, e.g. " 1" for one minute, so that the
cipher appears just to the right of center.

Neither of the following work. They both convert to the number zero
instead of to the actual number of minutes.

ActiveCell.Value = Val(LTrim(ActiveCell.Value))
ActiveCell.Value = LTrim(ActiveCell.Value)

Using ActiveCell.Text for the right-hand expression also doesn't seem
to work.

The cell formatting in the column has been converted to number, zero
decimal places.

Thanks for any help.

Fred Holmes

Oh_Kay. . . !

It works, but why the heck do they do that? Or is it just part of
the code that the html frames use?

Many, many thanks for your help.

Fred Holmes


On Wed, 27 Apr 2005 16:10:34 +0100, "Bob Phillips"
wrote:

Fred,

Try this as well

With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With

OK, Now I'm trying to do the following:


Sub Convert_to_value_3()
Dim c As Cell
For Each c In Selection
With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With
Next c
End Sub

"Cell" doesn't work as an object type. If I substitute "Range" for
"Cell" the code will process only the first cell in the selected
Range, not all cells in the selected range.

Many thanks for any help.

Fred Holmes


On Wed, 27 Apr 2005 16:10:34 +0100, "Bob Phillips"
wrote:

Fred,

Try this as well

With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With

Sub Convert_to_value_3()
Dim c As Range
For Each c In Selection
With c
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With
Next c
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fred Holmes" wrote in message
...
OK, Now I'm trying to do the following:


Sub Convert_to_value_3()
Dim c As Cell
For Each c In Selection
With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With
Next c
End Sub

"Cell" doesn't work as an object type. If I substitute "Range" for
"Cell" the code will process only the first cell in the selected
Range, not all cells in the selected range.

Many thanks for any help.

Fred Holmes


On Wed, 27 Apr 2005 16:10:34 +0100, "Bob Phillips"
wrote:

Fred,

Try this as well

With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With

It's done by the HTML, non-breaking spaces used to align data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fred Holmes" wrote in message
...
Oh_Kay. . . !

It works, but why the heck do they do that? Or is it just part of
the code that the html frames use?

Many, many thanks for your help.

Fred Holmes


On Wed, 27 Apr 2005 16:10:34 +0100, "Bob Phillips"
wrote:

Fred,

Try this as well

With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With

Fred:

I would use
Dim rng As Range
Dim c As Range
Set rng = Selection
For Each c in rng.Cells

HTH
Ed

"Fred Holmes" wrote in message
...
OK, Now I'm trying to do the following:


Sub Convert_to_value_3()
Dim c As Cell
For Each c In Selection
With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With
Next c
End Sub

"Cell" doesn't work as an object type. If I substitute "Range" for
"Cell" the code will process only the first cell in the selected
Range, not all cells in the selected range.

Many thanks for any help.

Fred Holmes


On Wed, 27 Apr 2005 16:10:34 +0100, "Bob Phillips"
wrote:

Fred,

Try this as well

With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With

Well, while the single-cell code works, the following attempt to
process a range fails. The code runs, but it produces the value "2"
in every cell in the selected range, no matter what the actual text
number in that cell happens to be.

Thanks for any help,

Fred Holmes

On Wed, 27 Apr 2005 16:46:56 +0100, "Bob Phillips"
wrote:


Sub Convert_to_value_3()
Dim c As Range
For Each c In Selection
With c
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With
Next c
End Sub

Sorry, missed a bit

Sub Convert_to_value_3()
Dim c As Range
For Each c In Selection
With c
.Replace Chr(160), ""
.Value = Val(LTrim(.Value))
End With
Next c
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fred Holmes" wrote in message
...
Well, while the single-cell code works, the following attempt to
process a range fails. The code runs, but it produces the value "2"
in every cell in the selected range, no matter what the actual text
number in that cell happens to be.

Thanks for any help,

Fred Holmes

On Wed, 27 Apr 2005 16:46:56 +0100, "Bob Phillips"
wrote:


Sub Convert_to_value_3()
Dim c As Range
For Each c In Selection
With c
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With
Next c
End Sub

Sub Convert_to_value_3()
Dim c As Range
For Each c In Selection
With c
.Replace Chr(160), ""
.Value = Val(LTrim(c.Value))
End With
Next c
End Sub

--
HTH,
Bernie
MS Excel MVP


"Fred Holmes" wrote in message
...
Well, while the single-cell code works, the following attempt to
process a range fails. The code runs, but it produces the value "2"
in every cell in the selected range, no matter what the actual text
number in that cell happens to be.

Thanks for any help,

Fred Holmes

On Wed, 27 Apr 2005 16:46:56 +0100, "Bob Phillips"
wrote:


Sub Convert_to_value_3()
Dim c As Range
For Each c In Selection
With c
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With
Next c
End Sub

Even with the below-suggested fix, when I run the code, the value
produced in every cell in the range is "2" regardless of the text
number that is actually in the cell. Running Office 2000 on Win 2000
SP-4 with "all" fixes applied.

Fred Holmes

On Wed, 27 Apr 2005 08:51:29 -0700, "Ed"
wrote:

Fred:

I would use
Dim rng As Range
Dim c As Range
Set rng = Selection
For Each c in rng.Cells

HTH
Ed

"Fred Holmes" wrote in message
.. .
OK, Now I'm trying to do the following:


Sub Convert_to_value_3()
Dim c As Cell
For Each c In Selection
With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With
Next c
End Sub

"Cell" doesn't work as an object type. If I substitute "Range" for
"Cell" the code will process only the first cell in the selected
Range, not all cells in the selected range.

Many thanks for any help.

Fred Holmes


On Wed, 27 Apr 2005 16:10:34 +0100, "Bob Phillips"
wrote:

Fred,

Try this as well

With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With

Earlier message is incorrect. With this change it now works. Al
works.

I modified the wrong code the first time, and had With c instead of
With ActiveCell

Many, Many thanks for all your help.

Fred Homes

On Wed, 27 Apr 2005 08:51:29 -0700, "Ed"
wrote:

Fred:

I would use
Dim rng As Range
Dim c As Range
Set rng = Selection
For Each c in rng.Cells

HTH
Ed

"Fred Holmes" wrote in message
.. .
OK, Now I'm trying to do the following:


Sub Convert_to_value_3()
Dim c As Cell
For Each c In Selection
With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With
Next c
End Sub

"Cell" doesn't work as an object type. If I substitute "Range" for
"Cell" the code will process only the first cell in the selected
Range, not all cells in the selected range.

Many thanks for any help.

Fred Holmes


On Wed, 27 Apr 2005 16:10:34 +0100, "Bob Phillips"
wrote:

Fred,

Try this as well

With ActiveCell
.Replace Chr(160), ""
.Value = Val(LTrim(ActiveCell.Value))
End With