Pasting from web into Excel - Excel converts into mathmatical - how to prevent

[David Smithz]

Hi there,

Frustratingly when I copy from a website with a big table of data that some
of the columns contain variously formatted international telephone numbers,
and then paste this data into Excel, all the long telephone numbers come out
as numerical values.

For example
4454344348579544
comes out as:

4.454345+11

Even when I prepare the cells to be formatted as Text, this is still
ignored. I have tried paste special, but I only get the option of Text, HTML
and Unicode and none give the desired result.

Can anyone help me finally resolve this frustrating problem.

Kind regards

Dave

 

[Ardus Petus]

Format your column with format 0

HTH

 

[David Smithz]

Format your column with format 0

Can you explain more. I tried formatting the column before hand as a text
column, but to no avail. Should I format the column before or after? And is
format 0 a particular type of format?

Thanks

 

[Ardus Petus]

Format before OR after pasting

Format>Cell>Number
select Personalized
select 0

 

[Tom Ogilvy]

The suggested solution is saying let the data be pasted as a number, then
after the fact, format the number to display all the digits instead of the
scientific notation you complain of. However, since your number has 16
characters, it would give you an incorrect result. Excel only handles about
15 significant digits, so it would truncate your number.

 

[GottaRun]

Yes, please explain.
format 0 seems to be an option from the custom drop-down list.

My problem is I have mm:ss data that I want treated as text instead of
date/time. Excel insists on appending :00 to any value greater than
23:59.

I tried format 0 both before pasting & after pasting. After pasting,
format 0 changed everything to a 1??

Ideally want I want is to tell excel that all my 'pasted' data should
be treated as text.

What's the secret?.

 

[David Biddulph]

Yes, please explain.
format 0 seems to be an option from the custom drop-down list.

My problem is I have mm:ss data that I want treated as text instead of
date/time. Excel insists on appending :00 to any value greater than
23:59.

I tried format 0 both before pasting & after pasting. After pasting,
format 0 changed everything to a 1??

Ideally want I want is to tell excel that all my 'pasted' data should
be treated as text.

What's the secret?.

If you want something formatted as text to stay that way, try Paste Special/
Values

 

[GottaRun]

paste special as text on tabular data from a web site puts most of the
data in the first column. It no longer spreads the columns of pasted
data into its own excel columns

the tabular data I want to 'copy&paste' looks like this:

1) OGRODOWICZ MARK M47 6306 BROOKLYN NY 533 446 41 24:00 23:25
7:33 20:58

2) CHAO DUSTIN M35 3738 NEW YORK NY 534 447 155 24:02 23:47
7:40 23:16

The paste should put this data into 2 rows, 12 columns each. All data
is text.