Text to Numbers

[jstevens]

I have approximately 400,000 numbers that are imported into Excel 2007 and are coming in as text. Is there a way to convert the entire range to numeric values in one sweep? I have tried formatting the range as "accounting and number" but that did not work.

untitled.png

Thanks,
John

[HansV]

Select the cells.
Make sure that they are formatted as General or as a number, not as Text.
Press Alt+F11 to activate the Visual Basic Editor.
Press Ctrl+G to activate the Immediate window.
Type

Selection.Value = Selection.Value

and press Enter.

If the values are in one column, an alternative is to select them, then click Text to Columns in the Data tab of the ribbon. Click Finish without changing any settings.

[jstevens]

Hans,

One other thing relative to the 400,000 records.

I am trying to purge 200 different Entities and their account records from the list. I have tried applying an advanced filter on the entire record set (400,000) but it is taking quite a while to process. I know the advanced filter code works as I tested it on a smaller record set.

Your thoughts are appreciated.
John

[HansV]

Excel is bound to be slow with 400,000 records. Does sorting the records by Entity make it easier?

Another option would be to import the data into a database (Access or SQL Server), manipulate them and then if desired, export them back to Excel (I'd probably keep on using the database instead).