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.

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.

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.

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

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.