Excel 2003 Problem - Numbers that don't work like numbers !

[Ken Frost]

Using Excel from Office 2003 Pro under Win 2000

I copied some financial figures from the web into an excel spreadsheet - I
have formatted them and when I place the cursor on them they appear to be
numbers but they will not add up in any calculations using them ! It is
quite bizarre - calculations just come to Zero no matter what. I have tried
formatting them several different ways but they don't appear to be
recognised as numbers by excel. I can send someone the file if they have
any idea what has gone wrong 1.49kb zippep.

Any help would be much appreciated

Thanks

 

[Paul Corrado]

Ken

This may work

Place the number 1 in an empty cell and select Edit/Copy

Select the range of numbers you downloaded and select Edit/Past Special and
check the Multiply option.

With any luck that will convert the text to numbers.

 

[Niek Otten]

Hi Ken,

In en empty cell (formatted as Number, just to be sure!), enter the number
1.
Edit>Copy
Select your numeric-like data. Edit>Paste Special, check Multiply. Now the
data is numeric.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[kkknie]

Try this:

Put the number 1 somewhere in your spreadsheet.
Select it and copy it (Ctrl-C).
Select the range of Bizzare numbers.
Right click and choose Paste Special and select the Multiply button.

This works most times.

 

[Dave Peterson]

You got a couple of very similar responses.

One version copied a cell with a 1 in it and multiplied.
The other version copied an empty cell and added.

I used to use the 1 and multiply version until I noticed that my blank/empty
cells were changed to 0 when I multiplied. The empty cells remain empty with
adding the empty cell (MT,MT,MT!)

 

[Ken Frost]

You got a couple of very similar responses.

One version copied a cell with a 1 in it and multiplied.
The other version copied an empty cell and added.

I used to use the 1 and multiply version until I noticed that my
blank/empty cells were changed to 0 when I multiplied. The empty
cells remain empty with adding the empty cell (MT,MT,MT!)

Thanks ver much for the replies guys ..... but no luck. After clicking
the multiply I can see the number as before, the formatting has changed
slightly but Excel still does not see these as numbers - it is too weird.
Am I allowed to post the spreadsheet here for someone to look at ? It is
1.5kb zipped. Any further ideas would be welcomed here.


Thanks very much for the thought and the time.

 

[Dave Peterson]

Did you get the data from a web page?

If yes, maybe you've got those pesky HTML non-breaking spaces in the cells.

See David McRitchie's site to clean them up:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[RagDyer]

If Dave is right, and you do have "non-breaking" spaces carried over from
the web, and these numbers are in a column, you could eliminate them without
getting into macros if you wish.

'Text To Columns" can handle both leading and trailing Char(160)'s.

Select the "bad" numbers, and
<Data> <TextToColumns>,
Make sure "delimited" is checked, then <Next>,
Click "other", and in the next box tell TTC what to look for.
Hold down <Alt>, and then, using the num keypad, *not* the numbers under the
function keys, enter
0160
You will *not* see anything displayed in the box after you type this.
Then click <Finish>.

If your problem *was* the "non-breaking" CHAR(160) spaces, you should now
have *real* XL numbers.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Did you get the data from a web page?

If yes, maybe you've got those pesky HTML non-breaking spaces in the cells.

See David McRitchie's site to clean them up:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Ken Frost]

If Dave is right, and you do have "non-breaking" spaces carried over

Tried the trick with the Text to Column - again no luck - they still look
like numbers, they edit like numbers but XL cannot see them as numbers.
Any further suggestions appreciated.

Thanks

 

[Dave Peterson]

Get a copy of Chip Pearson's CellView addin:
http://www.cpearson.com/excel/CellView.htm

You can use that to find out what's in the cell.

Post back with your results for one of the offending cells.

 

[Ken Frost]

I downloaded Cellview and it was the 160 character that was the probem -
I had tried the Text to columntrick before without success (I was
obviously doing something wrong) - this time it worked - bingo.

Thanks to all of you for the help.

 

[RagDyer]

Thanks for the feed-back.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

I downloaded Cellview and it was the 160 character that was the probem -
I had tried the Text to columntrick before without success (I was
obviously doing something wrong) - this time it worked - bingo.

Thanks to all of you for the help.