I copied some table with products and prices from a web page to a Excel (2013) worksheet.

Both the source table and my own Excel are configured with French number settings: a thousand euros and 20 cents is formatted 1 000,20 €

Problem: I cannot get the price column (which contains values from 3 000,00 to 5 000,00, with no currency unit) to be recognized as numbers by Excel.

I have tried all the solutions(1) mentioned here with no success: How to convert text to numbers in Excel ...

(1) except writing a VBA script and retyping the values. Solutions based on "clicking an error message" are not applicable as there is no error message even after setting the Number type on the cells.

Is there a solution here ?


I finally found an efficient solution by pure chance: paste the HTML into a text editor, then copy-paste the raw text into Excel -- > all numbers were automatically recognized.

Using Excel Paste Special function and pasting the HTML as text also works.

| improve this answer | |

I've always had the pastespecial>multiply and paste in a 1 work fine for me.

Perhaps it isn't recognizing the "," properly. Do a search replace of commas and maybe blanks in the column, see if that helps. If not try multiplying in the 1 again.

| improve this answer | |
  • I have tried replacing the whitespaces with ctrl-H but Excel did not find any. Certainly because of the HTML nature of the pasted text. – Sébastien Aug 20 '15 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.