Format Questions

[Bob]

Hello,

Thanks for all the previous help.

I have been copying a list of frequencies from a web site into Excel.
I imagine that I am copying as Text; would this be so ?

I have pre-formatted the column that I am pasting into as "Numbers".
For the most part, works O.K., as I can modify them as numbers, and do
numerical ops on them.

But, for some, after the pasting, Excel apparently (still) thinks
they are text.

When I try to do a numerical op on them, I get an error msg.
No apostrophe evident in formula bar preceding the number
(or whatever Excel thinks it is)

a. any general thoughts on why this may be ?

b. If a cell is pre-formatted as a Number, and a piece of text such as
2976 is pasted into it, should Excel now think that it is a number ?

c. If a piece of text such as 2976 is pasted into a cell that has been
formatted as Text, if I then go to the cell
and say, now, to format that cell as a Number, will it, and will Excel
now accept it a number ?

d. If a cell is formatted as Number, and a numerical value is pasted
into it, will it "always" appear on the right hand side ?
I know it does if I write it in via the keyboard, but is it the same for
Pasting in ?

Thanks,
Bob

 

[Gord Dibben]

Can you post the URL for the list you are copying into Excel?

Might give us an idea.

Much depends upon the formatting of the source data fields......leading or
trailing spaces for one.

Those will copy in as text rather than numbers.

Excel usually makes "best guess" but no hard rules can be stated.

One trick you can use is to format all to number or general.

Copy an empty cell.

Select all data and edit>paste special(in place)>add>ok>esc

This usually forces Excel to treat all as numbers.


Gord Dibben MS Excel MVP

 

[Bob]

Hi,

Thanks for help.

Here's the URL from which I'm copying:

http://www.monitoringtimes.com/html/mttopHF.html

Good hints. Thanks,

Regards,
Bob

 

[Gord Dibben]

I copied a few hundred of those "numbers" and the associated text.

Pasted to Excel then ran through Data>Text to Columns using fixed width to break
into two columns.

The "number" column is text due to each cell having 4 non-breaking spaces wirh
the numbers.

Get rid of those and the numbers become real numbers.

The trick I showed you will not work with these cells.

A non-breaking space is found in HTML data.

The code for NB<SP> is 0160

So................Select the column and Edit>Replace

What: Alt + 0160

With: nothing

When I say Alt + 0160 I mean hold the Alt key then type 0160 on the
NumPad......not above the qwerty keys.


Gord

 

[Bob]

Hi,

Much thanks for help; greatly appreciated.
And, for your time.

Regards,
Bob
---------------------