I want to create a database in which one of the columns will contain a 12-digit account number. I currently have the info in a Word document and I have messed around a little with copying and pasting the data into Excel.
It works, except for the account number column. After pasting, I get not a 12-digit number but what appears to be some sort of shorthand or formula in Excel.
Example:
I copy the number 780016582931 from Word.
When I paste it into Excel I get: 7.80017E+11
I’m sure this makes perfect sense to someone who knows Excel.
What is this and how do I get my 12-digit number into my column?
Excel is treating your number as a number (surprise!) Because it thinks it’s a number, and it’s a big one, it converts it to floating point and displays it in scientific notation. 7.80017E+11 means 7.80017 × 10[sup]11[/sup].
Select the column with your account numbers, right-click it and choose Format, and make them strings instead of numbers.
As a shortcut, you can prefix all your account numbers with an apostrophe. That will tell Excel to trat them as strings.
Alternately, you may just have too widen your column to see the whole thing, but then the value would still be treated as a number. You may want that; you may not want that. It would prove problematic if you had account numbers that begin with a zero.
It doesn’t automatically convert numbers to floating point if you are in “Number” format. It will if you are in “General” format, which is the default mode. You’ll have to get rid of the two zeroes to the right of the decimal point in “Numbers” format, but just do that by clicking the appropriate ZERO tab in the tool bar. My version of Excel does not have “Strings” as an option in FORMAT.
ETA: Widening the column width doesn’t change the format in my version of Excel.
Those are two different solutions with two very different consequences. And the OP definitely needs to pick one or the other; a mishmash is different again.
The “strings” format is usually called “text” in the format selection UI.
If all the account numbers are uniform, you can custom-format the cells. I don’t have Excel in front of me right now, but if I recall, you go to Format Cells, choose Custom from the drop-down and then put in numerals (zeroes) corresponding to your account numbers. So 000000000000 would allow for a twelve-digit account number. Entering a 1 in the cell would result in 000000000001 being displayed, etc.
If Excel is converting my very big number to scientific notation so it will fit in a column, why doesn’t widening the column prevent Excel from doing this?
mmm
Widening the column is only for the use of us humans so that you can see it the entire width of the column. It does not change the data type that is stored in the column.
An account number should not be stored in a data type such as Integer, floating point etc. It will never be used to be calculated against other data.
I don’t have Excel in front of me, and am certainly less than an expert (I’m a database guy).
Define the column as general or text or whatever before you cut and paste into it.
I would agree that Excel, even in cut and paste should automatically create a column type (long Int?) that would store it for what you want.
I’ve dealt with this when trying to import 10 digit phone numbers into Excel. It ‘helpfully’ screwed everything right up.
Define the column as general or text before you cut and past into it.
The critical thing is that cells in Excel are NOT database fields. There is no such thing as a “type” of a cell. If it contains all digits, Excel stores it as a numeric value that’s good with arithmetic. Regardless of your desires to the contrary.
What you can do with a cell (or group of cells or column or row) is apply a *format *to the cell. Along with the column’s width, this controls how the information is displayed. It does NOT control, or restrict, what the information is. Which is utterly backwards from how columns are defined in a database.
So the OP’s situation, where he has a series of digits that he does NOT want to consider as a numeric value, there is no GOOD pure reliable answer. There are several half-assed answers = workarounds, each with different limitations.
That is only sometimes true. For instance Oracle stores all numbers as packed decimals regardless of how they are displayed or used. If you extracted the 12 digit account number in the OP from an Oracle database you need a TO_CHAR or else it would be produced in the same manner.
Formatting the destination column as text prior to pasting is the usual answer. Pasting from the clipboard into Excel can still cause the cell to revert to the General format. You can either use the down arrow below the Paste icon to choose Match Destination Formatting or past to Notepad and recopy from there.
Or, chose “Paste Special” and then “Values only”. I use that a lot when copying from one spreadsheet to another.
ETA: I also hate Excel. It does not operate quite like the other MS application. You need to dedicate a certain amount of grey matter to be “Excel only”, instead of just general “Software knowledge”.
The biggest thing is that Excel is not a database. It’s also not a word processor. But a hell of a lot of people use it as one or the other.
Which jobs it does half-assedly compared to dedicated desktop tools like Access or Word.
What really gets silly is when somebody tries to use Excel as a database for megabytes or gigabytes of important data. Don’t laugh; people do it. :smack: