text to values
 

In Excel2000, what is the best way to code a macro that will convert a
column of text to values. For example, I have the following in column
A:

'100
'1000
'20

I want this to be converted to values like this:

100
1000
20

Thanks

text to values
 

Snax,

You could use a formula such as in cell B1

=RIGHT(A1,LEN(A1)-1) where A1 was your '100 value.

Just copy the formula down.

In essence, it says, take the right most characters, except the very first
charcter (length - 1).

Hope that helps.

Regards,
Kevin





"snax500" wrote in message
om...
In Excel2000, what is the best way to code a macro that will convert a
column of text to values. For example, I have the following in column
A:

'100
'1000
'20

I want this to be converted to values like this:

100
1000
20

Thanks

text to values
 

Sorry, disregard my answer...not a macro.

Kevin


"Kevin Stecyk" wrote in message
...
Snax,

You could use a formula such as in cell B1

=RIGHT(A1,LEN(A1)-1) where A1 was your '100 value.

Just copy the formula down.

In essence, it says, take the right most characters, except the very first
charcter (length - 1).

Hope that helps.

Regards,
Kevin





"snax500" wrote in message
om...
In Excel2000, what is the best way to code a macro that will convert a
column of text to values. For example, I have the following in column
A:

'100
'1000
'20

I want this to be converted to values like this:

100
1000
20

Thanks

text to values
 

As a matter of interest:
nor would your formula approach work since the single apostrophe is viewed
as a formatting character and would be ignored by the formula. So the
formula would remove the first digit on the left as well.

=VALUE(A1)
would work.


Sub Converttonumbers()
for each cell in selection.specialcells(xlconstants,xltextvalues)
if isnumeric(cell.value) then
cell.Formula = cdbl(cell.Value)
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"Kevin Stecyk" wrote in message
...
Sorry, disregard my answer...not a macro.

Kevin


"Kevin Stecyk" wrote in message
...
Snax,

You could use a formula such as in cell B1

=RIGHT(A1,LEN(A1)-1) where A1 was your '100 value.

Just copy the formula down.

In essence, it says, take the right most characters, except the very
first
charcter (length - 1).

Hope that helps.

Regards,
Kevin





"snax500" wrote in message
om...
In Excel2000, what is the best way to code a macro that will convert a
column of text to values. For example, I have the following in column
A:

'100
'1000
'20

I want this to be converted to values like this:

100
1000
20

Thanks

text to values
 

in B1 try this:
=value(a1)

if you have blanks:
=if(a1="","",value(a1))

this is using a formula instead of code.

James


-----Original Message-----
In Excel2000, what is the best way to code a macro that
will convert a
column of text to values. For example, I have the
following in column
A:

'100
'1000
'20

I want this to be converted to values like this:

100
1000
20

Thanks
.

text to values
 

Try:

Sub ConvertText()
Columns("A:A").TextToColumns Destination:=Range("A1")
End Sub

In om,
snax500 typed:
In Excel2000, what is the best way to code a macro that will convert a
column of text to values. For example, I have the following in column
A:

'100
'1000
'20

I want this to be converted to values like this:

100
1000
20

Thanks