# Need Excel help please **URL:** \ **Category:** Factual Questions\ **Created:** [June 17, 2017, 11:46am UTC](https://boards.straightdope.com/t/need-excel-help-please/789180 "2017-06-17T11:46:55Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Mean\_Mr.Mustard](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mean_mr.mustard/32/3865_2.png) [@Mean\_Mr.Mustard](https://boards.straightdope.com/u/Mean_Mr.Mustard)\ **Post date:** [June 17, 2017, 11:46am UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/1 "2017-06-17T11:46:55Z")
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? Thanks! mmm ---
**Author:** ![friedo](https://avatars.discourse-cdn.com/v4/letter/f/8edcca/32.png) [@friedo](https://boards.straightdope.com/u/friedo)\ **Post date:** [June 17, 2017, 11:52am UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/2 "2017-06-17T11:52:45Z")
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. ---
**Author:** ![Jack\_Batty](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jack_batty/32/3201_2.png) [@Jack\_Batty](https://boards.straightdope.com/u/Jack_Batty)\ **Post date:** [June 17, 2017, 12:13pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/3 "2017-06-17T12:13:00Z")
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. ---
**Author:** ![John\_Mace](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/john_mace/32/185_2.png) [@John\_Mace](https://boards.straightdope.com/u/John_Mace)\ **Post date:** [June 17, 2017, 12:21pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/4 "2017-06-17T12:21:15Z")
> [@friedo](#): > > 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. 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. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [June 17, 2017, 12:34pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/5 "2017-06-17T12:34:45Z")
@ **friedo** ’s suggestion. 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. ---
**Author:** ![Mean\_Mr.Mustard](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mean_mr.mustard/32/3865_2.png) [@Mean\_Mr.Mustard](https://boards.straightdope.com/u/Mean_Mr.Mustard)\ **Post date:** [June 17, 2017, 4:32pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/6 "2017-06-17T16:32:53Z")
I guess I need someone to define a ‘string’ for me. Widening the column was the first thing I tried, did not work. These are account numbers, non number-numbers. IOW, I won’t need to add them, or even sort them; I just need them attached to the name on that row. While we’re at it, anything I should know about importing Word data into Excel? Thanks all, mmm ---
**Author:** ![John\_Mace](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/john_mace/32/185_2.png) [@John\_Mace](https://boards.straightdope.com/u/John_Mace)\ **Post date:** [June 17, 2017, 4:51pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/7 "2017-06-17T16:51:22Z")
Just change the format to “Number”. ---
**Author:** ![bob\_2](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bob_2/32/3341_2.png) [@bob\_2](https://boards.straightdope.com/u/bob_2)\ **Post date:** [June 17, 2017, 4:56pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/8 "2017-06-17T16:56:37Z")
> [@John\_Mace](#): > > Just change the format to “Number”. Shouldn’t that be “text”? ---
**Author:** ![enipla](https://avatars.discourse-cdn.com/v4/letter/e/54ee81/32.png) [@enipla](https://boards.straightdope.com/u/enipla)\ **Post date:** [June 17, 2017, 5:16pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/9 "2017-06-17T17:16:08Z")
> [@Mean\_Mr.Mustard](#): > > I guess I need someone to define a ‘string’ for me. > > Widening the column was the first thing I tried, did not work. > > These are account numbers, non number-numbers. IOW, I won’t need to add them, or even sort them; I just need them attached to the name on that row. > > While we’re at it, anything I should know about importing Word data into Excel? > > Thanks all, > mmm A ‘string’ field/column definition will not be used for any mathematical calculations. It can be 123, ABC or 1AC. 123 in a string field/column can’t be calculated against. 123 would be considered to be simple text. As account numbers will not be used in calculations. A string field would be fine. ---
**Author:** ![Jack\_Batty](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jack_batty/32/3201_2.png) [@Jack\_Batty](https://boards.straightdope.com/u/Jack_Batty)\ **Post date:** [June 17, 2017, 5:40pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/10 "2017-06-17T17:40:23Z")
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. ---
**Author:** ![Mean\_Mr.Mustard](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mean_mr.mustard/32/3865_2.png) [@Mean\_Mr.Mustard](https://boards.straightdope.com/u/Mean_Mr.Mustard)\ **Post date:** [June 17, 2017, 6:45pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/11 "2017-06-17T18:45:51Z")
OK, I think I understand. Except for one thing… 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 ---
**Author:** ![enipla](https://avatars.discourse-cdn.com/v4/letter/e/54ee81/32.png) [@enipla](https://boards.straightdope.com/u/enipla)\ **Post date:** [June 17, 2017, 7:48pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/12 "2017-06-17T19:48:48Z")
> [@Mean\_Mr.Mustard](#): > > OK, I think I understand. > > Except for one thing… > > 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. ---
**Author:** ![enipla](https://avatars.discourse-cdn.com/v4/letter/e/54ee81/32.png) [@enipla](https://boards.straightdope.com/u/enipla)\ **Post date:** [June 17, 2017, 8:02pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/13 "2017-06-17T20:02:44Z")
> [@enipla](#): > > \I’ve dealt with this when trying to import 10 digit phone numbers into Excel. It ‘helpfully’ screwed everything right up.\ 1234560016 became- 1234560020 Took me a bit to figure out. It was ‘helpfully’ rounding the number for me. Up or down. I hate Excel. ---
**Author:** ![John\_Mace](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/john_mace/32/185_2.png) [@John\_Mace](https://boards.straightdope.com/u/John_Mace)\ **Post date:** [June 17, 2017, 8:28pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/14 "2017-06-17T20:28:54Z")
> [@enipla](#): > > 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. Emphasis added. Please see post #4. Cell format defaults to “General” which creates the problem the OP is trying to solve. ---
**Author:** ![enipla](https://avatars.discourse-cdn.com/v4/letter/e/54ee81/32.png) [@enipla](https://boards.straightdope.com/u/enipla)\ **Post date:** [June 17, 2017, 8:56pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/15 "2017-06-17T20:56:01Z")
> [@John\_Mace](#): > > Emphasis added. Please see post #4. Cell format defaults to “General” which creates the problem the OP is trying to solve. My mistake. It is certainly a cell format problem for the OP. So Format the column as char (character, AKA - String or Text) I suspect. (I don’t have Excel in front of me) ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [June 17, 2017, 9:36pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/16 "2017-06-17T21:36:53Z")
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. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [June 17, 2017, 10:52pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/17 "2017-06-17T22:52:54Z")
> [@LSLGuy](#): > > 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. 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. ---
**Author:** ![John\_Mace](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/john_mace/32/185_2.png) [@John\_Mace](https://boards.straightdope.com/u/John_Mace)\ **Post date:** [June 17, 2017, 10:57pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/18 "2017-06-17T22:57:45Z")
> [@don\_t\_ask](#): > > 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”. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [June 17, 2017, 11:04pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/19 "2017-06-17T23:04:54Z")
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: ---
**Author:** ![John\_Mace](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/john_mace/32/185_2.png) [@John\_Mace](https://boards.straightdope.com/u/John_Mace)\ **Post date:** [June 17, 2017, 11:15pm UTC](https://boards.straightdope.com/t/need-excel-help-please/789180/20 "2017-06-17T23:15:31Z")
It’s not bad for arranging pictures on a page, though! [Next page](https://boards.straightdope.com/t/need-excel-help-please/789180.md?page=2)