Help - Excel 97 Macro convert Text to Number

  • Thread starter Thread starter John Thomas Smith
  • Start date Start date
J

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
 
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
 
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
Click to expand...

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)
Click to expand...

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
 
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
Click to expand...
....

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)
Click to expand...

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
 
Back
Top