The forums in this section of DUG are no longer accepting new post, but you can still get lots of value from the old posts here. Please visit the active forums to comment/post new questions (choose which product you are interested in):
In an attempt to copy some data from a table online into Excel something goes wrong.
The table has 5 columns. Name, Amount, Amount2, Prercentage1 and Percentage2. The problem is that all the columns are imported as text. And I need them to be a number.
I used to remember that there was a functions where you could take this amount or percentage and "convert" it into "number". The other problem is that the amount fields are formatted like this: "$0.01" and the percentage fields like "15.15%". And I can't just ask the provider of the data to change the format! Or natively download it like an excel file!
Have a great day, Erik
try to paste into notpad, then copy that and paste into excel as pure text. Then you copy text to coumns, and select the correct format column by column. Its generally the easiest way.
Another option is to view the page source and copy that. Then open WebExpresion and paste the HTML. Then copy from webexpression back to Excel.
David Singleton - MVP Dynamics NAV
Dynamics NAV Consultant since 1991
Available for Navision system
Well I tried the web to text to excel already. That didn't work.
I don't have WebExpression - but I do have Visual Studio. I'll try to copy in there.
Tried that! Didn't work either!
What I do is paste to Excel. Change the Excel columns to the proper type i.e. text, number, percent, etc. Do your formatting now.
Clear contents on the Excel worksheet and then do a second paste and it straightens everything out.
I'm very sad to say that none of the suggested solutions so far has worked any wonders. So I'm still interested in suggestions.
In reply to Erik P. Ernst:
One more suggestion, a clumsy one:
Don't open .html with Excel (which is possible), so you avoid all automatic column type suggestions done by Excel.
Create new Excel sheet AND FORMAT columns as needed - Numeric, Percentage, whatsoeverThen Ctrl+C / Ctr+V your data, follow that Numeric data hits column you set format as Numeric, etc.
I use this approach copying (Ctrl+C / Ctr+V) data from Navision ListForms to Excel, but my problem was just opposite - I have Dim Values left-padded with zeroes, simply pasting such in Excel ends up with Numeric format (loosing zeroes), and these can't be converted to Text simply by selecting & formatting. Opposite is true also - if Excel has decided that cell is Text, you can't convert it to Numeric by formating.
Only way - if A1 contains 3.1415926 as Text, set A2 = A1*1, then select A2, Ctrl+C, leave selection on A2 and "Paste Value", then delete A column.
Rather idiotic method, but it still works, and MS hasn't offered something more applicable. The algorithm of guessing (and applying) data types for cells upon entering data just works in such a manner, and for unknown reasons, once it decided that you entered, say, Date, you can try formatting the cell as anything else till second appearance of Jesus Christ, but in vain - it WILL remain Date (or whatsoever Excel decided)
MCP-Dynamics NAV Application
In reply to Modris Ivans:
Messed up with cell refs in my generic sample...
Modris Ivansif A1 contains 3.1415926 as Text, set A2 = A1*1, then select A2, Ctrl+C, leave selection on A2 and "Paste Value", then delete A column.
it should be:
if A1 contains 3.1415926 as Text, set B1 = A1*1, then select B1,
Ctrl+C, leave selection on B1 and "Paste Value", then delete A column.
Posting new one, as I'm not allowed to EDIT my previous post...