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
--
AP

"David Smithz" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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
>
>

[David Smithz]

"Ardus Petus" <> wrote in message
> 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

[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?.

[Ardus Petus]

Format before OR after pasting

Format>Cell>Number
select Personalized
select 0

"David Smithz" <[email protected]> a écrit dans le message de
news:[email protected]...
>
> "Ardus Petus" <> wrote in message
> > 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
>
>

[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.

--
Regards,
Tom Ogilvy



"David Smithz" <[email protected]> wrote in message
news:[email protected]...
>
> "Ardus Petus" <> wrote in message
> > 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
>
>

[David Biddulph]

"GottaRun" <[email protected]> wrote in
message news:[email protected]...
>
> 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
--
David Biddulph

[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.