Help - Excel 97 Macro convert Text to Number

[John Thomas Smith]

I have a monthly process where I bring a range of cells
into a spreadsheet (copy-paste) and I need the information
to be numbers but it is created (out of my control) as text

such as...
'1.23
'1.24
'1.25
and so on for about a hundred rows

It is really cumbersome to have to keep pressing F2 to edit,
home to get to the ' and then delete and enter to remove the
' mark and convert the text to a number

I used the Macro recorder (with relative value set) but it
picks up the absolute value of the first cell and then when I
run the macro it puts that value into each cell

What I want to do, but don't know how, is edit the macro so it
works on the current cell to go to the beginning of the cell
and remove the ' to make text into number, and then activate
the Enter key to go down to the next cell to be ready for me
to press Ctrl-t to run again (until the end of the data)

What I have from the macro recorder (plus my attempt to edit,
which does not work and stops with an error) is...

Sub Txt2Num()
'
' Txt2Num Macro
' Macro recorded 10/3/2005 by Mailroom
'
' Keyboard Shortcut: Ctrl+t
' ActiveCell.FormulaR1C1 = "1.23"
'
ActiveCell = Value(ActiveCell)
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

I **think** I need to do something with the Value command,
but it does not work

Could someone post the code to operate on the "activecell"
to go to the start of the cell and remove the ' so the text
entry will convert to numeric?

Thanks Much !!!

John Thomas Smith
http://www.direct2usales.com
http://www.pacifier.com/~jtsmith

[Bob Phillips]

John,

Try this

Sub Txt2Num()
Dim i As Long

For i = 1 To Cells(Rows.Count,"A").End(xlUp)
Cells(i,"A").Value = Val(Cell(i,"A").Value)
Next i
End Sub

HTH
Bob

"John Thomas Smith" <[email protected]> wrote in message
news:[email protected]...
>I have a monthly process where I bring a range of cells
> into a spreadsheet (copy-paste) and I need the information
> to be numbers but it is created (out of my control) as text
>
> such as...
> '1.23
> '1.24
> '1.25
> and so on for about a hundred rows
>
> It is really cumbersome to have to keep pressing F2 to edit,
> home to get to the ' and then delete and enter to remove the
> ' mark and convert the text to a number
>
> I used the Macro recorder (with relative value set) but it
> picks up the absolute value of the first cell and then when I
> run the macro it puts that value into each cell
>
> What I want to do, but don't know how, is edit the macro so it
> works on the current cell to go to the beginning of the cell
> and remove the ' to make text into number, and then activate
> the Enter key to go down to the next cell to be ready for me
> to press Ctrl-t to run again (until the end of the data)
>
> What I have from the macro recorder (plus my attempt to edit,
> which does not work and stops with an error) is...
>
> Sub Txt2Num()
> '
> ' Txt2Num Macro
> ' Macro recorded 10/3/2005 by Mailroom
> '
> ' Keyboard Shortcut: Ctrl+t
> ' ActiveCell.FormulaR1C1 = "1.23"
> '
> ActiveCell = Value(ActiveCell)
> ActiveCell.Offset(1, 0).Range("A1").Select
> End Sub
>
> I **think** I need to do something with the Value command,
> but it does not work
>
> Could someone post the code to operate on the "activecell"
> to go to the start of the cell and remove the ' so the text
> entry will convert to numeric?
>
> Thanks Much !!!
>
> John Thomas Smith
> http://www.direct2usales.com
> http://www.pacifier.com/~jtsmith

[John Thomas Smith]

On Mon, 3 Oct 2005 11:28:57 -0700, "Bob Phillips"
<[email protected]> wrote:

>Sub Txt2Num()
>Dim i As Long
>
> For i = 1 To Cells(Rows.Count,"A").End(xlUp)
> Cells(i,"A").Value = Val(Cell(i,"A").Value)
> Next i
>End Sub

Stopped with an error on Cell, so (not knowing what I am
doing) I changed to...

> Cells(i,"A").Value = Val(Cells(i,"A").Value)

That runs... but does not remove the ' at the start

I still wind up with '1.23 '1.24 '1.25 and so on in the
cells... did not remove the ' as I need


John Thomas Smith
http://www.direct2usales.com
http://www.pacifier.com/~jtsmith

[Debra Dalgleish]

There are instructions here for converting text numbers to real numbers,
either manually or programmatically:

http://www.contextures.com/xlDataEntry03.html

John Thomas Smith wrote:
> I have a monthly process where I bring a range of cells
> into a spreadsheet (copy-paste) and I need the information
> to be numbers but it is created (out of my control) as text
>
> such as...
> '1.23
> '1.24
> '1.25
> and so on for about a hundred rows
>
> It is really cumbersome to have to keep pressing F2 to edit,
> home to get to the ' and then delete and enter to remove the
> ' mark and convert the text to a number
>
> I used the Macro recorder (with relative value set) but it
> picks up the absolute value of the first cell and then when I
> run the macro it puts that value into each cell
>
> What I want to do, but don't know how, is edit the macro so it
> works on the current cell to go to the beginning of the cell
> and remove the ' to make text into number, and then activate
> the Enter key to go down to the next cell to be ready for me
> to press Ctrl-t to run again (until the end of the data)
>
> What I have from the macro recorder (plus my attempt to edit,
> which does not work and stops with an error) is...
>
> Sub Txt2Num()
> '
> ' Txt2Num Macro
> ' Macro recorded 10/3/2005 by Mailroom
> '
> ' Keyboard Shortcut: Ctrl+t
> ' ActiveCell.FormulaR1C1 = "1.23"
> '
> ActiveCell = Value(ActiveCell)
> ActiveCell.Offset(1, 0).Range("A1").Select
> End Sub
>
> I **think** I need to do something with the Value command,
> but it does not work
>
> Could someone post the code to operate on the "activecell"
> to go to the start of the cell and remove the ' so the text
> entry will convert to numeric?
>
> Thanks Much !!!
>
> John Thomas Smith
> http://www.direct2usales.com
> http://www.pacifier.com/~jtsmith


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Harlan Grove]

John Thomas Smith wrote...
>I have a monthly process where I bring a range of cells
>into a spreadsheet (copy-paste) and I need the information
>to be numbers but it is created (out of my control) as text
>
>such as...
>'1.23
>'1.24
>'1.25
>and so on for about a hundred rows
>
>It is really cumbersome to have to keep pressing F2 to edit,
>home to get to the ' and then delete and enter to remove the
>' mark and convert the text to a number
....

You don't need a macro to do this if these are all in a single column.
Just select all cells in that column, issue the menu command Data >
Text to Columns and click on the Finish button.

>What I want to do, but don't know how, is edit the macro so it
>works on the current cell to go to the beginning of the cell
>and remove the ' to make text into number, and then activate
>the Enter key to go down to the next cell to be ready for me
>to press Ctrl-t to run again (until the end of the data)

If you must use a macro, try


Sub foo()
Dim i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
With Cells(i, "A")
If Not IsEmpty(.Value) And IsNumeric(.Value) Then _
.Value = CDbl(.Value)
End With
Next i
End Sub

[John Thomas Smith]

On Mon, 03 Oct 2005 15:48:00 -0400, Debra Dalgleish
<[email protected]> wrote:
>There are instructions here for converting text numbers to real numbers,
>either manually or programmatically:
> http://www.contextures.com/xlDataEntry03.html

Thanks DATA ==> TEXT TO COLUMNS did the job!

John Thomas Smith
http://www.direct2usales.com
http://www.pacifier.com/~jtsmith

[John Thomas Smith]

On 3 Oct 2005 15:10:39 -0700, "Harlan Grove" <[email protected]> wrote:
>You don't need a macro to do this if these are all in a single column.
>Just select all cells in that column, issue the menu command Data >
>Text to Columns and click on the Finish button.

Thanks... works perfectly!

John Thomas Smith
http://www.direct2usales.com
http://www.pacifier.com/~jtsmith