Converting Text to Numbers
 

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

Converting Text to Numbers
 

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.

--
Regards,
Tom Ogilvy



"Charles May" wrote:

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

Converting Text to Numbers
 

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 wrote:

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

--

Dave Peterson