Converting Text to Numbers

[Charles May]

I have a spreadsheet that is exported from Quickbooks (QB). I have added a
custom field in QB to enter a cost for a line item on an invoice. The
problem is that this is only a text field and QB does not perform any
calculations on it. So...
I have decided to export it to an existing spreadsheet in excel and run a
macro against it.

I recorded a macro and in the process I selected the range of "text numbers"
using the Shift+End+Down Arrow combination. Then I click the error box and
select convert text to numbers. The fields change in excel but the macro
doesn't capture it. Can this be done?

Basically I want to take a selected range and convert the numbers that are
stored as text to an actual number so I can perform calculations against
them.


Any help is appreciated

Thanks

Charlie

 

[Tom Ogilvy]

Sub ConverttoNumbers()
Dim cell as Range
for each cell in selection
if isnumeric(trim(cell)) then
cell.numberformat = "General"
cell.Value = 1*Trim(cell.value)
end if
Next
End if

you can also put 1 in and empty cell, copy it, select the cells and do
edit=>Paste Special and Select multiply

format the cell to general before you do it.

 

[Dave Peterson]

If your data is in a single column, you could record a macro when you do:

Data|Text to columns
and click Finish.

 

[Charles May]

Thanks!!
Much appreciated.

Charlie