Crazy Excel Quirk

Posted on 2012-08-22
Medium Priority
Last Modified: 2012-08-22
Hello-- I am using Excel 2007, and when I try to paste the string "487684E40" into a cell with format set to "General," Excel is automatically assuming the "E" represents an exponent so it's "helping me out" and automatically changing the format to "Scientific" and changing "487684E40" to "4.88E+45."

How do I shut this functionality off? I need it to stay format "General" and value "487684E40" when I paste it in.

Thanks a lot!
Question by:Jeff9687
  • 2
LVL 26

Expert Comment

ID: 38322789
You can put a single quote " ' " in the cell prior to pasting.

Author Comment

ID: 38322822
I can't alter the data coming in... it's being pulled in from a database via sql code embedded in a VBA module I wrote, and the string is in the database as "487684E40".
LVL 26

Accepted Solution

pony10us earned 1000 total points
ID: 38322839
LVL 26

Assisted Solution

redmondb earned 1000 total points
ID: 38322901
Hi, Jeff9687.

Can you define the format of the cell the value is being pasted into as "Text"?

If so, it should display it as "487684E40". However, Excel can be funny about "Text" formatting so it's best to then "extract" the data to a General-formatted cell by using a formula such as


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question