I am creating an Excel database that will have well over a thousand names and account numbers. I am copying and pasting from an existing Word document.
I have the names all entered and am now filling in the account number column. Each account number runs 12 digits, so when I copy/paste, I get some funky formula or shorthand instead of my 12 digits.
To convert the formula to digits, I have to hit CTRL 1, then click on Numbers, then change the default from 2 decimal places to zero. Once this is done, the account numbers look like I want them to look (displaying all 12 digits).
Here is what I’d like to solve, though. I am doing this in batches of 50 or so numbers, then converting. I want the column to default to zero decimal places so I don’t have to keep jumping through the hoops as described above.
I have tried selecting the column, doing the CTRL 1 dance, and saving, but that does not retain the formatting for that column.
This should be easy to fix, I would think, but I sure can’t figure out how.
That should work, but also get into the habit of using Text instead of Number for this. Text will always display every digit whereas Number truncates after 15 (or 16.) Cutting off the last few digits of the account numbers if they are long is definitely going to result in some bad times.
What’s happening is, Excel defaults to pasting the content (number or text) and the formatting. So the formatting gets copied over from Word which overrides the number formatting you’ve defined for the column.
As RaftPeople said, one of the “paste special” options should allow you to copy just the content. But I also agree that you should be entering account IDs as text, not numbers.
There’s a difference between storing a number as text and formatting a number as text. When you are entering data manually, you’d preface the number with a single quote to enter it as text. The most noticeable difference is, if you do this, you can enter a number that starts with zero and that zero is displayed. If the number is stored as a number but formatted as a text, the leading zero disappears (and isn’t even stored), because entering 0245 is considered the same as entering 245.
I’m not sure how to paste a number as text, and I don’t know if that would be preferable or necessary in this case.
Use notepad (or other text editor that isn’t a word processor) to create a plain text document (.txt) and paste the numbers into that. Save that file, the use excels ‘import data’ tool to import that document into the selected column. As part of that import process, you can define the data type of each detected field (for a single list of numbers, it should only detect one field).
You could possibly use this tool to import data straight from the document, if there’s common separator between the fields (you could even create a common separator with some of Words advanced ‘find and replace’ tricks). If you have a lot of data, I’d strongly recommend this approach to semi-automate the whole process.
This did not work; in fact, it changed all my previously entered 12-digit numbers back to the undesirable format. That is, 780012213168 changed to 7.80012E+11.
When I select Paste Special, my only options are:
Microsoft Word Document Object
Picture
HTML
Unicode Text
Text
Selecting ‘text’ works, but the other hundreds of numbers I’ve already pasted I already converted to ‘number’ (with zero decimal places) to make it appear properly. I suspect this would cause trouble down the road if some entries are numbers and others are text.
mmm
You have to re-paste the values and then you will see all the digits and the cell will be considered text.
This implies your source data is text. (EDIT: maybe - I only got that dialog box for text but my tests were limited, mostly I copy from within excel so not sure what your source data really looks like internally)
If you format the column as number and then copy and paste text numbers into that column/cells then they should be interpreted as numbers in that column and no formatting should be switched (meaning it shouldn’t convert it to scientific notation).
I just tested it and no problem.
It seems like your original sequence of steps and the one when you tested “Paste Special” must have some difference because your results don’t quite add up between the two scenarios.
In summary, this should work (it works on my version which is 2017):
1 - Format column/cells as Number
2 - Copy data (which appears to be just text numbers with many digits)
3 - Paste (doesn’t require paste special because the source doesn’t have any formatting based on the results of your paste special test)
Hoping to add to understanding rather than give another recipe that will fail. It’s very fraught trying to teach this stuff via messageboard. We can’t see what you’re doing, we don’t know what you know and we don’t know what you may think you know that’s actually wrong.
Every cell in Excel has a value and it has a format. The format controls how the value looks, not what the value is. You can change the format without changing the value. If you want to see the actual value in a cell, put the highlight on that cell & press F2 and look at the edit line just below the menu & tab strip. What you see there is what’s really in the cell. Then press Esc to cancel the edit.
So what you describe does not mean your typing was wasted. It just means your format in that/those cell(s) is not what you want it to be.
Ultimately the problem is that a “part number” isn’t a number. It’s a string of digits. It makes no sense to perform arithmetic on part numbers. You can’t add part number “123” to part number “456” and get part number “579”. But Excel wants very deeply to believe that whenever you type some digits into a cell, you’re trying to input a number; something to do arithmetic on. To Excel it makes sense that *of course *you want to be able to add 123 to 456 and get 579. So we have to push back against that tendency just a bit. We want Excel to accept the idea that it’s just a string of characters that each happen to be one of 1, 2, 3, 4, 5, 6, 7 ,8, 9, & 0 with no A, B, C, …, Z in there too.
I’m going to stop here and if any of this helps with understanding, let me know and we’ll try the next step.
You want “text”. The paste function doesn’t care about excel data types (number, time, boolean, etc.), it just wants to know how you want the metadata (information about the text, like spacing, formatting, etc.) handled. “Text” means ignore the metadata - use only the plain text.
Why don’t you just use the format painter? It’s on the Home tab, Clipboard section, and looks like a paintbrush. Click a correct cell, double click the Format Painter, and click the cells that are incorrect.