Leading Zeros and Phone numbers
 

I have to work on any number of spread sheets that end up having the
leading "Zero" that is "0" removed...

At present I have a sheet with 32K rows of details and phone numbers...
The problem is that where it is a Mobile number the leading "0" has
been removed.

ie. 0404122345 is now 404122345

How do a do a lookup so that I can add / insert a leading "0" where any
of the phone numbers start that with a 4 have a leading 0 inserted?

Have tried to use the Filter function... But this feature appears to not
work in Office 2007, as expected. Select a Number Filter and "Begins
with" 4 ... Always returns Zero results.

Then depending on what format the col is set to, it removes the leading
zero any way...

..... Any one care to provide some training... North side Brisbane..
Office 2003 worked, but I can not get 2007 to play ball. It must be me.

Leading Zeros and Phone numbers
 

Say the phone numbers are in column A. If a leading zero has been dropped,
then the length of the number will be 9 rather than 10. In B1 enter:

=IF(LEN(A1)=10,A1,"0" & A1) and copy down.

This restores any missing zeros. If you copy column B and
paste/special/value back onto column A, you will have the column correctly
formatted as Text with the correct values.
--
Gary''s Student - gsnu2007j


"mad_dad" wrote:

I have to work on any number of spread sheets that end up having the
leading "Zero" that is "0" removed...

At present I have a sheet with 32K rows of details and phone numbers...
The problem is that where it is a Mobile number the leading "0" has
been removed.

ie. 0404122345 is now 404122345

How do a do a lookup so that I can add / insert a leading "0" where any
of the phone numbers start that with a 4 have a leading 0 inserted?

Have tried to use the Filter function... But this feature appears to not
work in Office 2007, as expected. Select a Number Filter and "Begins
with" 4 ... Always returns Zero results.

Then depending on what format the col is set to, it removes the leading
zero any way...

..... Any one care to provide some training... North side Brisbane..
Office 2003 worked, but I can not get 2007 to play ball. It must be me.

Leading Zeros and Phone numbers
 

Hi

=TEXT(A1,"0000000000")

The formula converts any number to text string at least 10 characters long.
When the number was shorter, leading 0's are added.

Arvi Laanemets


"mad_dad" wrote in message
...
I have to work on any number of spread sheets that end up having the
leading "Zero" that is "0" removed...

At present I have a sheet with 32K rows of details and phone numbers...
The problem is that where it is a Mobile number the leading "0" has been
removed.

ie. 0404122345 is now 404122345

How do a do a lookup so that I can add / insert a leading "0" where any of
the phone numbers start that with a 4 have a leading 0 inserted?

Have tried to use the Filter function... But this feature appears to not
work in Office 2007, as expected. Select a Number Filter and "Begins
with" 4 ... Always returns Zero results.

Then depending on what format the col is set to, it removes the leading
zero any way...

.... Any one care to provide some training... North side Brisbane.. Office
2003 worked, but I can not get 2007 to play ball. It must be me.