Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with changing cell contents with $ sign
We are copying and pasting a document from the internet and when the dollar
amounts are being pasted into Excel there is a dollar sign in front of the number, which is not allowing us to use the sub-total function in Excel. We've tried to re-format, to no avail. I've also used hunt and replace to take out the dollar sign, still, same result. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with changing cell contents with $ sign
Probable that the numbers are imported as Text.
Format all to General. Copy an empty cell and then select your range of data then EditPaste Special(in place)AddOKEsc. Gord Dibben MS Excel MVP On Tue, 19 Dec 2006 10:14:00 -0800, MMangen wrote: We are copying and pasting a document from the internet and when the dollar amounts are being pasted into Excel there is a dollar sign in front of the number, which is not allowing us to use the sub-total function in Excel. We've tried to re-format, to no avail. I've also used hunt and replace to take out the dollar sign, still, same result. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with changing cell contents with $ sign
That didn't work....any other ideas?
"Gord Dibben" wrote: Probable that the numbers are imported as Text. Format all to General. Copy an empty cell and then select your range of data then EditPaste Special(in place)AddOKEsc. Gord Dibben MS Excel MVP On Tue, 19 Dec 2006 10:14:00 -0800, MMangen wrote: We are copying and pasting a document from the internet and when the dollar amounts are being pasted into Excel there is a dollar sign in front of the number, which is not allowing us to use the sub-total function in Excel. We've tried to re-format, to no avail. I've also used hunt and replace to take out the dollar sign, still, same result. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with changing cell contents with $ sign
It might be one of those HTML non-breaking space characters.
David McRitchie has a macro that cleans up this kind of stuff: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm MMangen wrote: We are copying and pasting a document from the internet and when the dollar amounts are being pasted into Excel there is a dollar sign in front of the number, which is not allowing us to use the sub-total function in Excel. We've tried to re-format, to no avail. I've also used hunt and replace to take out the dollar sign, still, same result. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with changing cell contents with $ sign
Gord Dibben wrote...
Probable that the numbers are imported as Text. And if these numbers come from an HTML table in a web page, more probable still that they have trailing HTML nonbreaking spaces, which are a PITA to eliminate. Format all to General. Unnecessary. Copy an empty cell and then select your range of data then EditPaste Special(in place)AddOKEsc. Doesn't help when cells contain HTML nonbreaking spaces. Easiest way to deal with this is to select the range containing the imported cells and run the menu command Edit Replace ([Ctrl]+H should work for the fashionable set who've moved on to Excel 2007). The Replace with field in the Find and Replace dialog should be blank/empty in order to DELETE the HTML nonbreaking space characters - tab to this field and press [Delete]. Then tab to the Find what field, press [Delete], then hold down either [Alt] key and press in sequence the 0, 1, 6 and 0 keys in the numeric keypad and release the [Alt] key. This should appear to have typed a space into the Find what field. Click on the Replace All button. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the cell contents from decimal to fractional | Excel Worksheet Functions | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Automatically Changing Cell Reference's when Pasting in Excel | Excel Discussion (Misc queries) | |||
Changing cell contents so that Excel recognises it as a date | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |