Modify number display

[IanC]

I have a spreadsheet that I copy/paste the contents of a csv file to. This
file contains phone numbers which are displayed incorrectly in one of
two different ways.

01234567890 appears as 1234567890
These numbers will always begin with 01, 02, 03 or 07. There are also some
number that begin with 3. These display correctly, but may confuse an
automated solution.
441234567890 appears as 4.1235E+11

If I change the formatting of the cells to text I can then manually modify
the cell contents to display as expected. The problem with this is that each
time I add the contents of a csv I have to go through the whole process
again. There are approximately 2000 entries for each csv so I'm looking for
a quicker option.

Any ideas? Obviously I'm looking for two solutions for the two conditions.

 

[Pete_UK]

What numbers begin with a 3 that are valid? Do you mean some overseas
destinations like Netherlands (31), Belgium (32), France (33) etc? You
could show these as 0031, 0032, 0033 etc. How could you distinguish
between a call to one of these countries and a call to 03xx ? Is there
some other column which gives the call type?

I'm not sure how your example 441234567890 is valid, as it begins with
a 4. I suspect this is meant to be the dialling code for UK (0044)
which has had both leading zeros stripped, and in fact represents the
number 01234567890 (so the 44 prefix should be replaced with 0).

Do you have any DQ calls (118), which do not have leading zeros?

Have you tried opening the .CSV file with Notepad to see if the
leading zeros are in the file? Excel will strip them out, but if they
are present you can rename the file to .txt and use the Data Import
wizard to retain those leading zeros.

Hope this helps.

Pete

 

[IanC]

Hi Pete

(e-mail address removed)...

What numbers begin with a 3 that are valid? Do you mean some overseas
destinations like Netherlands (31), Belgium (32), France (33) etc? You
could show these as 0031, 0032, 0033 etc. How could you distinguish
between a call to one of these countries and a call to 03xx ? Is there
some other column which gives the call type?

Some of them could be overseas numbers, but others are not. It's a list of
phone calls and texts from a mobile phone. Most of the numbers beginning
with 3 are communications with Faceache (sorry, Facebook) and these all
begin with 32665 so it's fairly easy to identify these.

I'm not sure how your example 441234567890 is valid, as it begins with
a 4. I suspect this is meant to be the dialling code for UK (0044)
which has had both leading zeros stripped, and in fact represents the
number 01234567890 (so the 44 prefix should be replaced with 0).

Correct. They are UK numbers, though some appear with the preceding 0
instead.

Do you have any DQ calls (118), which do not have leading zeros?

Not at the moment, though again these are easily identified.

Have you tried opening the .CSV file with Notepad to see if the
leading zeros are in the file? Excel will strip them out, but if they
are present you can rename the file to .txt and use the Data Import
wizard to retain those leading zeros.

Just the answer I needed. As csv file default to opening with Excel, I
hadn't considered using Import Text File. It's just like me to look for a
complicated answer when a simple one is staring me in the face!

Hope this helps.

It certainly does! Thanks very much.

 

[Ron Rosenfeld]

I have a spreadsheet that I copy/paste the contents of a csv file to. This
file contains phone numbers which are displayed incorrectly in one of
two different ways.

01234567890 appears as 1234567890
These numbers will always begin with 01, 02, 03 or 07. There are also some
number that begin with 3. These display correctly, but may confuse an
automated solution.
441234567890 appears as 4.1235E+11

If I change the formatting of the cells to text I can then manually modify
the cell contents to display as expected. The problem with this is that each
time I add the contents of a csv I have to go through the whole process
again. There are approximately 2000 entries for each csv so I'm looking for
a quicker option.

Any ideas? Obviously I'm looking for two solutions for the two conditions.

Format the column as something like:

Type: 00000000000 (however many zero's your telephone numbers are)

or perhaps 00 000 000 0000

in order to retain the leading zero's without messing up other things.

 

[Pete_UK]

You're welcome, Ian - thanks for feeding back.

Pete

 

[Pete_UK]

Ron,

phone numbers can be of varying length, and some have no leading
zeros, most have one, and overseas destinations (landline and mobile)
have two, so a simple formatting to a fixed number of zeros is not
much use.

Besides, phone numbers are not numbers per se - you wouldn't carry out
arithmetic on them.

Pete

 

[Ron Rosenfeld]

Ron,

phone numbers can be of varying length, and some have no leading
zeros, most have one, and overseas destinations (landline and mobile)
have two, so a simple formatting to a fixed number of zeros is not
much use.

Besides, phone numbers are not numbers per se - you wouldn't carry out
arithmetic on them.

Pete

That's true, and with more information, appropriate formatting can be
easily applied. Given the limited amount of information provided,
"simple" formatting is all that can be expected.

In the US, for example,

[<=9999999]###-####;(###) ###-#### can differentiate between 7 and 10

digit phone numbers, but our numbers will never start with a zero.