Excel 2007: scientific notation confusion 1

[dawnd3]

Hi all, this is very frustrating. I have phone numbers in a spreadsheet that are formatted as "numbers". I want them to be text to export to an Access database. (there are only numbers, no dashes or dots) However, when I format the field to "text" or "general" the longer numbers turn into scientific notation. This seems backwards, as shouldn't it turn to scientific notation when converting to a number format? I know I can put a apostrophe in front of all the numbers, I was just hoping for a better solution. (and maybe even an explanation as to why it does this.)

Thanks,

Dawn

 

[SkipVought]

hi,

So do you expect to do arithmetic with your phone numbers?

faq68-6659.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dawnd3]

Skip, I understand when number is not a number and I had already read your other post before posting. I TRIED to change the format to text as well as general and that is when it converted to scientific notation. As a "number" it was fine, but I wanted to export it as text. I concatenated an apostrophe before each one and then changed to text format, but I was still perplexed as to why it would convert to scientific notation when formatting as text.

Thanks,

Dawn

 

[SkipVought]

Please post an example of a NUMBER that changed to SCIENTIFIC notation when you formatted as TEXT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dawnd3]

8059660999105

It is the phone number and extension run together as this is how they like to store their data.

Dawn

 

[SkipVought]

Principle 1:
NUMBERS are stored different than TEXT. When the NUMBER 1 is change to CHARACTER 1, a conversion occurs, that changes the value that is actually stored.

Principle 2:
Formatting changes NOTHING.

Therefore, YOU must make the conversion/change before changing the format. Otherwise, it remains a number and goes thru the Excel default formatting for long numbers bit.

Best as I can understand the issue.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dawnd3]

Then basically, what I did is what I was supposed to do, right? (add apostrophe before each number?)

Thanks

Dawn

 

[SkipVought]

yes!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

A quick way to convert a column of numbers to text (I think I got this from Skip) is Data,Text to Columns and at the last step highlight the column and choose text

Gavin

 

[SkipVought]

Gavin,

That will do it! However, I cannot take credit for that one. You will just have to suck it up and take it like a man!

And you'll have to BLAME someone else. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}