Convert A Text Field To Numeric On Import

[erajim]

I have a fields coming in that are text but should be numeric. is there a formula I can put in the cell to convert it from text to numeric?

The original database has the numeric fields stored as text, but I need to import them into Excel and do calculations on them.

Any assistance is GREATLY appreciated.

Thanks
jim

[Robert B]

Hi

Select the column in question, select Data from the taskbar, select text to Columns and press Finish on the Wizard.

HTH

Robert

[ByTheCringe2]

If you format the fields as Number before the import, it should convert.

Or you can use the VALUE function to convert them into a spare column, then use Copy, Paste Special, Values to overwrite the originals.

[erajim]

not possible to convert before hand. I am trying to create a template that will present the data and calculate values. The data is coming from a Lotus database that our programmers (external) do not have the time to convert the fields. So I need a function to convert the field to numeric so I can calculate values.
Thanks
Jim

[erajim]

Robert thanks for your help but what I am trying to do is have it automatically convert upon opening the excel worksheet.

Thanks

[ByTheCringe2]

If you can have an extra column to put the converted value in, use the formula

=VALUE(A1)

Otherwise you will need to use VBA. All other methods need you to do something after the import.

[erajim]

Thanks I will try that right now and get back to you.