Decimal point / comma, and switching text to a number

[andrewsutton]

This should be easy, but I haven't managed it.
First I open a CSV file in Excel, and save as .xls.

Then I open the Excel file. One of the columns is man-months worked, using a comma as the decimal separator. It is in text. Excel will not do calculations with it until I have made it into numbers. BUT to do this it seems it's not enough simply to go to format>cells>number. I have to click open each cell and then click 'enter'. It then accepts it as a number.

A quick way of doing this is to Find-And-Replace all commas with commas (i.e. change nothing but make Excel realise it's a number). Now here comes the problem...

I need to do this in a macro, and the computers that it will be run on are not set up the same. Some have commas as the decimal mark and some have points. I want to write code that will work whatever the settings are on the user's computer (UK or Brazil).

Ideally I'd like the computer to realise the numbers are numbers without and Find-And-Replace trickery. If that can't be done, I need the macro to know what the system's decimal mark is.

Thanks anyone who knows how!

Andrew

 

[jfuoti]

I am not sure if this will work, but have you ever tried using the Data>Text to Columns feature. This should allow you to change the text to numbers by going through the wizard instead of using the find-replace method.