Convert text to values

[mp]

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

 

[GS]

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

 

[mp]

mp formulated on Wednesday :

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

 

[GS]

mp pretended :

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

 

[Ron Rosenfeld]

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

 

[mp]

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

 

[isabelle]

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

--
isabelle



Le 2012-04-26 06:12, Ron Rosenfeld a écrit :

 

[Ron Rosenfeld]

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.

 

[Ron Rosenfeld]

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.

 

[Ron Rosenfeld]

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.

 

[Ron Rosenfeld]

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

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

 

[isabelle]

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 :