How to Convert Text to Number Programmatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel 2002-2003. I am copying portions of a workbook into another workbook.
The data in the original is financial ($dollars) but is stored in text
format. How may I convert en masse the text cells to currency cells? I have
tried:

Selection.Columns.NumberFormat = "$#,##0.00_);($#,##0.00)"

I do not get an error on this, but the cells are unchanged and a little
widget (an ! in a yellow diamond) comes up next to the cells that produces a
drop down menu that allows me to convert txt to number. Until I manually do
this, I cannot even manually change the cells to currency. How may I do this
all in code? Thanks.
 
Doug

One of the code snippets below should work. The first one takes all
'numbers' on a worksheet and converts them and formats as currency. The
second takes a pre-selected range and converts that. (Try it on a copy of
the data first)

Sub ConvAllTextNumbs()
With Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
.Value = .Value * 1
.NumberFormat = "$#,##0.00_);($#,##0.00)"
End With
End Sub

Sub ConvSelectedTextNumbs()
With Selection
.Value = .Value * 1
.NumberFormat = "$#,##0.00_);($#,##0.00)"
End With
End Sub



--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Dear Frank:

This is the code I put in line (wouldn't work as a sub):

WbNew.Worksheets(Jurisdiction).Range("B6:G38").Select
With Selection
.Value = .Value
.NumberFormat = "$#,##0.00_);($#,##0.00)"
End With

Thanks for the tip!
 
Dear Nick:

Dear Frank:

This is the code I put in line (wouldn't work as a sub):

WbNew.Worksheets(Jurisdiction).Range("B6:G38").Select
With Selection
.Value = .Value
.NumberFormat = "$#,##0.00_);($#,##0.00)"
End With

Thanks for the tip!
 
Back
Top