Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm setting up a list of books and in that list I'm including the ISBN13-number. Now eventhough I've formated the cells to be "Text", it still converts it to numbers. So 9781597495783 becomes 9,7816E+12. How can I prevent this from happening?

share|improve this question
Do you enter that number AFTER or before you format the cell? If its after the formatting, you have to use Data → text in columns to change the value. – nixda Jan 9 at 11:29
After. The thing is I'm going through an online bookstore to see what books the company should buy, and I copy+paste the ISBN13 to save time, and that's when it "reformats" to numbers. The afformentioned action worked, but is there no way to make Excel understand that it's text I want to insert and not numbers? – Sandokan Jan 9 at 11:33
Did you paste in the cell or in the bar on top? – John Siu Jan 9 at 11:42
Both render the same (wrong) result. – Sandokan Jan 9 at 11:42

3 Answers

up vote 1 down vote accepted

Try formatting the cell using Number instead of Text. Reduce the decimal places to 0, and click on OK.

enter image description here

share|improve this answer
up vote 0 down vote

The only way around this I know is to select the text in the function box, this will resolve it. I guess it depends on how many fields you're trying to update as to whether this is reasonable or not.

So, just click in the fx box where the ISBN number is, and this alone should correct it on the sheet.

enter image description here

Another work around is to select the Formulas Tab in the ribbon and under Formula Auditing, select Show Formula's (so if you only have ISBN's then this may work).

share|improve this answer
Nope, did nothing. Only poping up the dialouge box for entering a formula. The format does not change to the desired one. Still gonna go with nixda's suggestion. – Sandokan Jan 9 at 12:02
up vote 0 down vote

When you paste, you should get a Paste options "pop-up" button next to the pasted content. If you choose "use destination formatting" then it should honour the "Text" format you have previously set. (if you don't get a pop-up, check in File > options > advanced in case it is turned off)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.