Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unable to calculate Pasted HTML
Does anyone else do this but me? I am pasting HTML perfectly fine into Excel
but the numbers being pasted cannot be calculated. It's almost as if they are being pasted in some weird format. The format says general but it isn't acting like a general format. If you have the answer it would be greatly appreciated. -- David P. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unable to calculate Pasted HTML
When you paste HTML into Excel you often get other characters like the
non-breaking space (character 160) in among the digits. Consequently, Excel will treat the cell as containing text, rather than numbers. You can do Edit/Replace (CTRL-H) with the cells highlighted, and in the Find What panel type ALT-0160 (hold down the Alt key and type 0160 on the numeric keypad) and leave the Replace With panel blank, then click Replace All. If that doesn't convert them to numbers, then you can try multiplying by 1 - enter 1 into a blank cell somewhere, select that cell and click <copy. Then highlight the offending cells and Edit | Paste Special | Multiply (check) then OK and <Esc. Hope this helps. Pete On Aug 6, 11:18 pm, David P. wrote: Does anyone else do this but me? I am pasting HTML perfectly fine into Excel but the numbers being pasted cannot be calculated. It's almost as if they are being pasted in some weird format. The format says general but it isn't acting like a general format. If you have the answer it would be greatly appreciated. -- David P. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unable to calculate Pasted HTML
Hi,
You can import External Data using Web Query, then copy the range to the clipboard and paste those range as values in a new location in the worksheet, using the Paste Special command under the Edit menu. Procedure 1: Importing External Data Do the following: 1. On the Data menu, point to Insert External Data, and then click New Web Query. The New query dialog box is displays. 2. Click the Stop icon to stop the default url getting executed. 3. In the Address box, enter this url : http://quote.yahoo.com/ and click the Go button. 4. Click the yellow arrow next to the table you want to import. When you click the yellow arrow it appears as tick mark. Click all those portion of the web page you want to import by clicking the yellow arrow. 5. Click the Import button to return to the import Data dialog box. 6. Specify the cell location where you want the imported data to be displayed. There are two option, whether you want it at the existing location or in a New worksheet. In our case case let assume the cell position is =$E$31 7. Click Ok to return to the active Worksheet. The excel displays the following message "Getting Data..." and the External Data Toolbar is displayed 8. Click the refresh icon to refresh data . Procedure 2: Pasting the copied range as Values. 1. Slect the range of imported data. 2. On the Edit menu click Copy. 3. Click a new cell location. 4. On the Edit menu, click Paste Special. The Paste Special dialog box is displayed. 5. Under Pase section Values option. 6. Click Ok. This will paste thecontent from the clipboard. 7. Click a cell below the last cell. 8. In the Standard Toolbar, click the AutoSum icon to perform a sum function of the range. Challa Prabhu "David P." wrote: Does anyone else do this but me? I am pasting HTML perfectly fine into Excel but the numbers being pasted cannot be calculated. It's almost as if they are being pasted in some weird format. The format says general but it isn't acting like a general format. If you have the answer it would be greatly appreciated. -- David P. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unable to calculate Pasted HTML
You are both very kind and have provided a solution to my problem that I
consider revolutionary at this point in time (you know how that goes). Thanks again. -- David P. "Pete_UK" wrote: When you paste HTML into Excel you often get other characters like the non-breaking space (character 160) in among the digits. Consequently, Excel will treat the cell as containing text, rather than numbers. You can do Edit/Replace (CTRL-H) with the cells highlighted, and in the Find What panel type ALT-0160 (hold down the Alt key and type 0160 on the numeric keypad) and leave the Replace With panel blank, then click Replace All. If that doesn't convert them to numbers, then you can try multiplying by 1 - enter 1 into a blank cell somewhere, select that cell and click <copy. Then highlight the offending cells and Edit | Paste Special | Multiply (check) then OK and <Esc. Hope this helps. Pete On Aug 6, 11:18 pm, David P. wrote: Does anyone else do this but me? I am pasting HTML perfectly fine into Excel but the numbers being pasted cannot be calculated. It's almost as if they are being pasted in some weird format. The format says general but it isn't acting like a general format. If you have the answer it would be greatly appreciated. -- David P. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unable to calculate Pasted HTML
You're welcome - thanks for feeding back.
Pete On Aug 7, 5:50 pm, David P. wrote: You are both very kind and have provided a solution to my problem that I consider revolutionary at this point in time (you know how that goes). Thanks again. -- David P. "Pete_UK" wrote: When you paste HTML into Excel you often get other characters like the non-breaking space (character 160) in among the digits. Consequently, Excel will treat the cell as containing text, rather than numbers. You can do Edit/Replace (CTRL-H) with the cells highlighted, and in the Find What panel type ALT-0160 (hold down the Alt key and type 0160 on the numeric keypad) and leave the Replace With panel blank, then click Replace All. If that doesn't convert them to numbers, then you can try multiplying by 1 - enter 1 into a blank cell somewhere, select that cell and click <copy. Then highlight the offending cells and Edit | Paste Special | Multiply (check) then OK and <Esc. Hope this helps. Pete On Aug 6, 11:18 pm, David P. wrote: Does anyone else do this but me? I am pasting HTML perfectly fine into Excel but the numbers being pasted cannot be calculated. It's almost as if they are being pasted in some weird format. The format says general but it isn't acting like a general format. If you have the answer it would be greatly appreciated. -- David P.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevoiusly posted message: Unable to remove objects pasted into Excel from HTML grid | Excel Worksheet Functions | |||
Unable to delete html data pasted into a cell | Excel Discussion (Misc queries) | |||
pasted HTML text disappears | Excel Discussion (Misc queries) | |||
Unable to remove objects pasted into Excel from HTML grid | Excel Worksheet Functions | |||
HTML Table pasted to Excel | Excel Discussion (Misc queries) |