Converting Commas to Decimal points

    I have a Brazilian table from a web site that I am copying from IE and then pasting into Excel. Even though I format all the cells to Text before I paste Excel 'kindly' ignores all the current formatting and pastes the HTML text on top. - OK BUT the numbers like 500.000 (five hundred thousand in Brazil) becomes 500 (five hundred) - not very helpful when its the figures that I am after and they need to be accurate. Does anyone have any suggestions on how to stop Excel reformatting the cells (and I've tried paste special but that pastes everything into the first column - just as bad). Help - thanks

    If I have a one time problem like this I usually will open it in word first and to a replace.


    You can do this easily with the .'s you can also reformat to remove paragraphs or move them as well as tabs


    word allows for this using special characters like ^t tab and ^p for paragraph.


    More than you asked for but the word link is often under utilized when reformating files.

    Hi Anonymous


    A couple of things. Have you tried using Data>Get External data>New Web Query to get the data into Excel direct from the Web site? This has lots of different formatting options for the imported query.


    Also, after using Paste Special you could use Data>Text to columns... to split up the data into seperate columns.

    Thanks for the suggestions. I was hoping to avoid using another app in between as this has to be a fast process about 100 get processed every day, so the more steps that have to be taken slows the whole process down and brings in the more chances of mistakes on the user side, but I'll take a look anyway. The text to columns fuction is no good as there is no consistency in line length and it would take far too long for someone to go through adjusting each line individually. I was hoping for something all in Excel that would leave the formatting from the HTML code as it is instead of doing conversions that are downright dangerous where accuracy is concerned. I'll try the New Web Query option and see how that works. Thanks anyway.

    what's the web site address ?


    I don't mind giving it a go to see how I get on.....


    from memory, I usually try paste unicode, although it does sound like you'll need to do some formatting within your process

    The web site is http://www.cvm.gov.br but you'll have to be able to read Portuguese if you want to try it. I've tried the web query but as all the pages I'm requesting data from are Active Server Pages then the web query will not work and just returns no data but as they only work for preformatted tables this isn't suprising. I'm still no nearer to finding an answer and may have to resort to writing ana pp that does the whole process and just ignore Excel altogether. The best option would be ablt to paste the HTML code into a brazilian enabled version of excel that already recognises . as , Can anyone think of a way to do this? Thanks in Advance.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login