Funky formatting in phone numbers

[sawilliams]

Hello:

I'm cleaning up a ton of data and I've imported a number of Excel spreadsheets into my database. One of the fields is "PHONE" and, after spending a lot of time fixing messy records, I see that many of my phone numbers have taken on some weird formatting. The original field in Excel was "general", not formatted in any particular manner. But here is a sample of what my phone numbers look like in Access:

PHONE
9084399510
9082420666
9082165186
9.89772e+009
9.78887e+009
9.7876e+009
9.78749e+009
9.78665e+009
9.78545e+009
9.78462e+009
9.73994e+009
9.73994e+009​

I can't find anyway to undo this. Looking at the original spreadsheets, the phone numbers appear normal. Any suggestions?

 

[SkipVought]

hi,

This is not at all "funky!" It is Scientific Notation! It's just a NUMBER! It's just a Number Format. Change the format!

However, are phone numbers, or rather SHOULD phone numbers be NUMBERS? Will you do arithmetic on these "numbers?"

They are, by category, an IDENTIFIER, and as such, ought to be TEXT.

faq68-6659

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sawilliams]

Skip:

Thanks for your spirited response. As a matter of fact, in the original Excel spreadsheet (I may not have been entirely clear) the phone field was "text". And, the field is also "text" in Access. I understand that Scientific Notation applies to numbers but why would it be applied to a text field? Hence, my use of the word "funky" to describe my data.

Looking back at my original spreadsheets, the "text" vesion of the phone numbers appear as text not numbers nor Scientific Notation. So, my problem remains...

 

[SkipVought]

It can happen in Excel, that the scientific notation format, occurred at the time the data was entered in the sheet and consequently the format was changed to text.

take the TEXT that you have, transfer it to Excel, change the FORMAT of the range to NUMBER with ZERO decimal places. If the text does not convert to numbers, then enter a number ONE in an empty cell, COPY the cell, SELECT the range of text. Then Edit > Paste Special -- MULTIPLY Now you will have NUMBERS.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sawilliams]

Skip:

Nifty, but when I did as you suggested, the resulting number was not the original phone number in the original spreadsheet. For example, in ny original worksheet, the phone number appears as "3479631029" turns into "3.47963e+009" in my Access db and when I do the paste special I get "3479630000".

What's so aggravating is that I fixed all the phone numbers in the original spreadsheets and they were text when I imported them into Access. I'm finding that I must save my Excel spreadsheet as a *.txt file (not even *.csv will work). Then, I need to reimport into Excel to Access or directly into Access. That additional step seems to remove whatever it is that's causing the odd modification. Unfortunately, I have to begin my entire project from scratch.

But thanks much for your assistance!

 

[SkipVought]

ahhhh.

You have lost PRECISION in TWO conversions!

Yes, you must return to the original Excel and make sure that the numeric conversion there is correct.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sawilliams]

Skip:

Yep, I'm back to square one. And, as I mentioned, if I save the spreadsheet as *.txt the formatting all goes away. I did not create the original spreadsheets and there are 5 altogether from different sources, so who knows how they were originally formatted.

But thanks for your help. I still learned some good stuff from our exchange.

 

[SkipVought]

So you have solved the mystery of the sci notation and there is joy in Mudville?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jsteph]

Saw,
Excel is goofy like that...and I think it was Skip who tipped me to the Paste-Special-Multiply trick. But since Excel is from birth a calculation tool, by default it will try to make everything it sees into a number, including dates. In fact, when you put numbers formatted as text into it, it flags the cells with an 'error' condition of 'Number stored as Text'...as if that's a bad thing(!?).

So you've definitely got to explicitly format as text, and make sure it's saved like that--and if your sheets are built in code it's a good idea to precede cell values that are digits--but you want stored as text--with a tic (Single-quote) as this will tell Excel that you really, really, really want cell to be stored as text, and it won't try to lord over you and make it's incorrect assumptions and 'help' you by physically changing your data without your consent.

In addition, when importing into Access' import tool--which also will 'assume' that data with digits is numeric--goes to bring the data in, you need to step through the import wizard and make sure those columns are imported as Text type. You can lose important stuff like leading-zeros that way, and of course the trap you found with the phone numbers.
--Jim

 

[sawilliams]

Skip & jsteph:

Sorry, I dropped off this string -- dragged into another project. And not exactly joy in Mudville but I'm plowing my way thru the data all over again. But your words of advice were greatly appreciated.

SAW