Got a credit card? use our Credit Card & Finance Calculators
Thanks to Wasron,jfgw,Rhyd6,eyeball08,Wondergirly, for Donating to support the site
Very big 'number' copied to Excel - overflow error?
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Very big 'number' copied to Excel - overflow error?
I've got an email with a table containing serial numbers in the form 8944110065252537496
When I copy and paste into Excel it converts to scientific format 8.9411E+18, and when I force back to full number I have lost 4 least significant digits thus: 8944110065252530000
Clearly (?) busting a limit - how do I get the serial number over intact?
I thought copy>paste special>text would work, but it doesn't, it still coverts to 8.9411E+18
thanks
AC
When I copy and paste into Excel it converts to scientific format 8.9411E+18, and when I force back to full number I have lost 4 least significant digits thus: 8944110065252530000
Clearly (?) busting a limit - how do I get the serial number over intact?
I thought copy>paste special>text would work, but it doesn't, it still coverts to 8.9411E+18
thanks
AC
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Very big 'number' copied to Excel - overflow error?
Answering my own question , looks like I have to format destination cells as text AND copy>paste special>text
-
- Lemon Slice
- Posts: 943
- Joined: November 4th, 2016, 11:33 am
- Has thanked: 32 times
- Been thanked: 462 times
Re: Very big 'number' copied to Excel - overflow error?
AleisterCrowley wrote:I've got an email with a table containing serial numbers in the form 8944110065252537496
When I copy and paste into Excel it converts to scientific format 8.9411E+18, and when I force back to full number I have lost 4 least significant digits thus: 8944110065252530000
Clearly (?) busting a limit - how do I get the serial number over intact?
I thought copy>paste special>text would work, but it doesn't, it still coverts to 8.9411E+18
thanks
AC
Prefix it with a single quote mark so it gets treated as text. (the quote mark won't be displayed).
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Very big 'number' copied to Excel - overflow error?
That would have been a pain with 80 lines in the table.
As mentioned formatting destination as text AND copy/paste as text works for me. Neither work on their own.
As mentioned formatting destination as text AND copy/paste as text works for me. Neither work on their own.
-
- Lemon Slice
- Posts: 943
- Joined: November 4th, 2016, 11:33 am
- Has thanked: 32 times
- Been thanked: 462 times
Re: Very big 'number' copied to Excel - overflow error?
AleisterCrowley wrote:That would have been a pain with 80 lines in the table.
As mentioned formatting destination as text AND copy/paste as text works for me. Neither work on their own.
If you can add the single quote before you copy the data, then it becomes a simple copy paste task to transfer the information.
This should be borne in mind if the source data is automatically collected, or (hint) you automate the import of the data.
Of course if this is just a one off task, it's not worth the hassle, but if it's something done on a regular basis, or done on such an irregular basis that you forget to do what's required to keep the data, it could be worth your while.
PochiSoldi
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Very big 'number' copied to Excel - overflow error?
It was a table embedded in an email, as a one off.
Not the way I would have done it...
Not the way I would have done it...
-
- Lemon Quarter
- Posts: 2574
- Joined: November 5th, 2016, 2:22 am
- Has thanked: 552 times
- Been thanked: 1212 times
Re: Very big 'number' copied to Excel - overflow error?
I just created a list of numbers(in notepad) using your example:
8944110065252537491
8944110065252537492
8944110065252537493
8944110065252537494
8944110065252537495
8944110065252537491
8944110065252537492
In Excel, I formatted the first five cells in a column to be "text", leaving the remainder as "general". Selecting the first cell in the column and then pasting the entire contents of the notepad list gave me the full numbers in the text-formatted cells, and the scientific notation in the general-formatted cells.
Excell does have a 15 significant digit limitation. You could google for add-ins that will overcome this, but it really depends on what you are trying to do with the data as to what would be useful?
VRD
8944110065252537491
8944110065252537492
8944110065252537493
8944110065252537494
8944110065252537495
8944110065252537491
8944110065252537492
In Excel, I formatted the first five cells in a column to be "text", leaving the remainder as "general". Selecting the first cell in the column and then pasting the entire contents of the notepad list gave me the full numbers in the text-formatted cells, and the scientific notation in the general-formatted cells.
Excell does have a 15 significant digit limitation. You could google for add-ins that will overcome this, but it really depends on what you are trying to do with the data as to what would be useful?
VRD
-
- Lemon Slice
- Posts: 943
- Joined: November 4th, 2016, 11:33 am
- Has thanked: 32 times
- Been thanked: 462 times
Re: Very big 'number' copied to Excel - overflow error?
vrdiver wrote:I just created a list of numbers(in notepad) using your example:
8944110065252537491
8944110065252537492
8944110065252537493
8944110065252537494
8944110065252537495
8944110065252537491
8944110065252537492
In Excel, I formatted the first five cells in a column to be "text", leaving the remainder as "general". Selecting the first cell in the column and then pasting the entire contents of the notepad list gave me the full numbers in the text-formatted cells, and the scientific notation in the general-formatted cells.
Excell does have a 15 significant digit limitation. You could google for add-ins that will overcome this, but it really depends on what you are trying to do with the data as to what would be useful?
VRD
Numbers starting with 8944 look like UK SIM card numbers, so I would guess that the OP is just treating them as text fields in a simple flat database.
-
- Lemon Half
- Posts: 6385
- Joined: November 4th, 2016, 11:35 am
- Has thanked: 1882 times
- Been thanked: 2026 times
Re: Very big 'number' copied to Excel - overflow error?
Yes , SIMs of course (my example jumbled for security reasons!) - I definitely had to perform both actions when copying from the email table - just formatting destination cells didn't work
-
- Lemon Quarter
- Posts: 2574
- Joined: November 5th, 2016, 2:22 am
- Has thanked: 552 times
- Been thanked: 1212 times
Re: Very big 'number' copied to Excel - overflow error?
When I copied a single number, I had the same result that you did, but copying the entire list in one go seemed to work OK.
(Windows 10, Office 2010)
VRD
(Windows 10, Office 2010)
VRD
Return to “Technology - Computers, TV, Phones etc.”
Who is online
Users browsing this forum: No registered users and 34 guests