I sometimes need to copy an HTML table into Excel to do further calculations. Often such table contains numbers in English locale while my Excel is configured for Danish locale.

This results in Excel treating the pasted numbers as text instead of numbers. The reason this occurs is the source data contains numbers such as 42.76 which Excel does not recognize as a number in Danish locale (it would expect 42,76).

To convert numbers stored as text in Excel into actual numbers, especially when dealing with regional differences in decimal separators, you can use the following methods:

Method 1: Text-to-Columns Tool

1. Select the entire column with your “text” numbers.
2. Navigate to the “Data” tab on the Excel Ribbon.
3. Click on “Text to Columns”.
4. Choose “Delimited”, then click “Next”.
5. Uncheck all delimiter options (like Tab, Semicolon, Comma, etc.), then click “Next”.
6. Select “General” under Column data format.
7. Click Advanced.
8. Change “Decimal separator” from , to . and “Thousand separator” from . to ,; click OK.
9. Click “Finish”.

This should convert your text to numbers.

Excel should treat these as actual numbers. If the decimal point is still not recognized due to the locale issue, proceed to the next method.

Method 2: Using Formula

Insert a new column beside your text numbers.

If your text number is in `A1 , enter the following formula in the new column:

=VALUE(SUBSTITUTE(A1,".",","))

This formula replaces the period (.) with a comma (,), then uses the VALUE function to convert the resulting text into a number.

Drag the formula down for the entire column to apply to all rows.

If you want to replace the old data, simply copy the new column and paste it as values (using “Paste Special” > “Values”) over the old text numbers.

The methods above are useful when dealing with issues arising from different regional settings. Adjustments may be needed based on specific circumstances or other regional considerations.