Decimal point / comma, and switching text to a number

andrewsutton

Board Regular
Joined
Oct 19, 2004
Messages
59
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jfuoti

New Member
Joined
Mar 26, 2002
Messages
48
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top