Convert text to values
 

cells have existing values - xls downloaded from a site- numbers come in
as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

Convert text to values
 

mp formulated on Wednesday :
cells have existing values - xls downloaded from a site- numbers come in as
text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to float,
reformat cell, replace value?

easier idea?

Thanks
Mark

See your original post in the other forum, where I added instructions
for how to use the formula I posted.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Convert text to values
 

On 4/25/2012 6:07 PM, GS wrote:
mp formulated on Wednesday :
cells have existing values - xls downloaded from a site- numbers come
in as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

See your original post in the other forum, where I added instructions
for how to use the formula I posted.


I saw that GS, thanks. I took that in the context of that forum on how
to do it manually. This similar query was in the context of automating
it via vb(a).

I will be doing various calcs on the values so would rather convert the
data type from string to real, rather than use (function x) rather than
(function(value x)) each time.

Thanks
Mark

Convert text to values
 

mp pretended :
On 4/25/2012 6:07 PM, GS wrote:
mp formulated on Wednesday :
cells have existing values - xls downloaded from a site- numbers come
in as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

See your original post in the other forum, where I added instructions
for how to use the formula I posted.


I saw that GS, thanks. I took that in the context of that forum on how to do
it manually. This similar query was in the context of automating it via
vb(a).

I will be doing various calcs on the values so would rather convert the data
type from string to real, rather than use (function x) rather than
(function(value x)) each time.

Thanks
Mark

Ok, Mark! I was going to offer a VBA solution but I'll hold off on that
for now. If you're interested to go that way then post back...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Convert text to values
 

On Wed, 25 Apr 2012 17:23:49 -0500, mp wrote:


cells have existing values - xls downloaded from a site- numbers come in
as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

for a VBA solution, assuming the cell's format is still "General", (the cell format shouldn't be changed by an import) you can simply do use this macro.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range of cells that need to be processed or define them some other way (and change the macro accordingly).

Then <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

======================
Option Explicit
Sub MakeNumeric()
Dim c As Range
For Each c In Selection
c.Value = c.Value
Next c
End Sub
=========================

Convert text to values
 

On 4/26/2012 5:12 AM, Ron Rosenfeld wrote:
On Wed, 25 Apr 2012 17:23:49 -0500, wrote:


cells have existing values - xls downloaded from a site- numbers come in
as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

for a VBA solution, assuming the cell's format is still "General", (the cell format shouldn't be changed by an import) you can simply do use this macro.

To enter this Macro (Sub),<alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range of cells that need to be processed or define them some other way (and change the macro accordingly).

Then<alt-F8 opens the macro dialog box. Select the macro by name, and<RUN.

======================
Option Explicit
Sub MakeNumeric()
Dim c As Range
For Each c In Selection
c.Value = c.Value
Next c
End Sub
=========================

I'll be darned, it's that easy? :-) I wouldn't have intuitively arrived
at that!! :-) thanks a bunch
Mark

Convert text to values
 

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

--
isabelle



Le 2012-04-26 06:12, Ron Rosenfeld a écrit :
On Wed, 25 Apr 2012 17:23:49 -0500, wrote:


cells have existing values - xls downloaded from a site- numbers come in
as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

for a VBA solution, assuming the cell's format is still "General", (the cell format shouldn't be changed by an import) you can simply do use this macro.

To enter this Macro (Sub),<alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range of cells that need to be processed or define them some other way (and change the macro accordingly).

Then<alt-F8 opens the macro dialog box. Select the macro by name, and<RUN.

======================
Option Explicit
Sub MakeNumeric()
Dim c As Range
For Each c In Selection
c.Value = c.Value
Next c
End Sub
=========================

Convert text to values
 

On Fri, 27 Apr 2012 19:47:46 -0400, isabelle wrote:

hi Ron,

why make a loop ?

To ensure one is only converting dates.

While selecting everything might work if I were absolutely certain of the data being converted, one might not want to convert, for example, text-numbers with leading zero's, or formulas that result in a numeric result, to their numeric values.

Convert text to values
 

On Fri, 27 Apr 2012 19:47:46 -0400, isabelle wrote:

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

And, of course, I neglected to put that test in in the macro I posted <sound of hand slapping forehead. I had it in something else I wrote, but neglected to copy it over.

Convert text to values
 

On Fri, 27 Apr 2012 18:12:25 -0500, mp wrote:

I'll be darned, it's that easy? :-) I wouldn't have intuitively arrived
at that!! :-) thanks a bunch
Mark

Mark,

For safety sake, I would suggest a little more complexity:

For Each c In Selection
If IsDate(c) Then c.Value = c.Value
Next c

Isabelle asked why not just:
Selection = selection.value

And the answer was to avoid converting non-date entries (including, for example, numbers with leading zero's that are in a text format; and also formulas). But I had omitted that test from what I posted earlier.

Convert text to values
 

On Fri, 27 Apr 2012 19:47:46 -0400, isabelle wrote:

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

--
isabelle

I should have added that what your version does what I posted without the loop; but does not do what I meant to post :-(

Convert text to values
 

phew, i was afraid for a moment that this solution was no longer possible with xl2007 +
thanks Ron, and have a good night

--
isabelle



Le 2012-04-27 20:15, Ron Rosenfeld a écrit :
On Fri, 27 Apr 2012 19:47:46 -0400, wrote:

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

--
isabelle

I should have added that what your version does what I posted without the loop; but does not do what I meant to post :-(