Why is Excel changing my numbers to formulas

Here's my problem: I have to recieve packages and input things like serial numbers, part numbers, and tracking numbers into various paperwork forms.<BR><BR>It is of course, generally preferable to use a barcode scanner to input 20-digit number strings, rather than hand-typing them. However, when I go to reformat them, Excel -insists- on converting the number to scientific notation--and since I'm editing the string into a non-number format (multiple spaces, parenthesis, etc), it will not un-fuck itself when I hit Enter. It's like this:<BR><BR>1) I scan the tracking number from the package. This gives me a solid string, such as 1234567890012345678900000.<BR><BR>2) I go to edit the number. What I -want- is what appears on the packing label: (123456789) 0012345678 9000000. But as soon as I go to edit the text--either by hitting F2, double-clicking on the cell, or clicking on the formula bar at the top, Excel re-displays the number as 1.23456789E-23 (or thereabouts). <BR><BR>3) If I'm foolish enough to try to edit this scientific-notation string, I merely end up with something like (1.23456) 789E-23, which is clearly unacceptable.<BR><BR>Is there a way to prevent Excel from 'converting' the format to scientific notation? Changing the value type to Text doesn't stop this from happening.

Set the cell format to text <B>before</B> entering any values. That should do the trick. Changing the format to text after a value has been entered won't do squat.

<blockquote class="ip-ubbcode-quote">
<div class="ip-ubbcode-quote-title">quote:</div>
<div class="ip-ubbcode-quote-content">Originally posted by El Guapo:<br>Set the cell format to text <b>before</b> entering any values. That should do the trick. Changing the format to text after a value has been entered won't do squat. </div>
</blockquote>What <b>El Guapo</b> said -- View image here: //episteme.arstechnica.com/groupee_common/emoticons/icon_smile.gif --<br><br>I will add that if you're a non Excel expert like me, that means you can change the number formatting of the cell, or a column by clicking the cell, or the column header & then right-clicking the highlighted cell, or range of cells & then clicking the "Number" tab & selecting "Text" as shown below.<br><br>this may be something you already know, but not every one uses Excel enough to know editing options.<br><br> -- View image here: //members.arstechnica.com/x/tangleweb/Format_Cells_Excel.png -- <br><br>If you are using a template, you can edit the properties of the cells, or entire columns & then re-save the template, so you don't have to mess with it again.<br><br>~Dave

You can also change it to the number format instead of text. If you do that you can do it after entering the value.<BR>Edit: I take that back. Depending on the length of the number you may lose precision formatting after.

No, definitely give it the Text format before entering the number. If you're using Excel 2003, you may want to turn off the annoying "Number stored as text" rule in Tools | Options | Error Checking.<BR><BR>Also, if these numbers all have the same length and desired format, you could really speed up your work using a formula. Enter the raw numbers in column A (in Text format). Then in cell B1, enter a formula such as this:<BR><pre class="ip-ubbcode-code-pre">="("&LEFT(A1,9)&") "&MID(A1,10,10)&" "&RIGHT(A1,6)</pre><BR>Then fill that formula down column B. (Change the numbers as necessary for your number format.) Select column B, copy, and do Edit | Paste Special | Value. You now have properly formatted numbers in column B, and you can go ahead and delete column A.

<BLOCKQUOTE class="ip-ubbcode-quote"><div class="ip-ubbcode-quote-title">quote:</div><div class="ip-ubbcode-quote-content">Originally posted by blargh:<BR>No, definitely give it the Text format before entering the number. </div></BLOCKQUOTE><BR>Ack, you're right - I just tested it, and with numbers that large you lose accuracy on the end - I wasn't aware of that issue before.

Yeah, it was the "change to text before entering data" trick that does it. I'd been trying to change the format to text after data was in the cell...<BR><BR>Thanks.<BR><BR><BLOCKQUOTE class="ip-ubbcode-quote"><div class="ip-ubbcode-quote-title">quote:</div><div class="ip-ubbcode-quote-content">Also, if these numbers all have the same length and desired format, </div></BLOCKQUOTE><BR>Oh, I <I>wish</I>.

<blockquote class="ip-ubbcode-quote">
<div class="ip-ubbcode-quote-title">quote:</div>
<div class="ip-ubbcode-quote-content">Originally posted by Magus`:<br><blockquote class="ip-ubbcode-quote">
<div class="ip-ubbcode-quote-title">quote:</div>
<div class="ip-ubbcode-quote-content">Originally posted by blargh:<br>No, definitely give it the Text format before entering the number. </div>
</blockquote>
<br>Ack, you're right - I just tested it, and with numbers that large you lose accuracy on the end - I wasn't aware of that issue before. </div>
</blockquote>And you'll definitely lose any leading zeroes, which is another very real problem when dealing with things like ID and tracking numbers.<br><blockquote class="ip-ubbcode-quote">
<div class="ip-ubbcode-quote-title">quote:</div>
<div class="ip-ubbcode-quote-content">Originally posted by Unforgiven:<br>Yeah, it was the "change to text before entering data" trick that does it. I'd been trying to change the format to text after data was in the cell...<br><br>Thanks.<br><br><blockquote class="ip-ubbcode-quote">
<div class="ip-ubbcode-quote-title">quote:</div>
<div class="ip-ubbcode-quote-content">Also, if these numbers all have the same length and desired format, </div>
</blockquote>
<br>Oh, I <i>wish</i>. </div>
</blockquote>Isn't that always the way. -- View image here: //cdn.arstechnica.net/forum/smilies/biggrin.gif -- Still, you may be able to set up a few template formulas for the various formats and just Ctrl-drag them around; might be faster than manually editing.