Inserting a "0"
 

Hello

In a spreadsheet containing UK phone numbers, all numbers should begin with
a zero, but when we pull off a report and copy and paste it into Excel,
depending on what's been input at source, some of the entries lack the zero.

Is there a way of formatting the column so that, if no zero appears at the
start of a phone number, one can be inserted at the start of each number?

Thanks.

Inserting a "0"
 

On May 8, 8:00 am, bollard wrote:
Hello

In a spreadsheet containing UK phone numbers, all numbers should begin with
a zero, but when we pull off a report and copy and paste it into Excel,
depending on what's been input at source, some of the entries lack the zero.

Is there a way of formatting the column so that, if no zero appears at the
start of a phone number, one can be inserted at the start of each number?

Thanks.

If your number is of fixed length (say xxxx-xxxx) you can format like
the following:

Format-Cells-Number-Custom-0000-0000

This will turn the number 1234567 into 0123-4567.

Inserting a "0"
 

Hi

If you format the receiving cells as Text before copying and pasting,
then the leading zero will be retained.

If you already have entries and some do not have leading zero's then you
could enter in a spare column
=IF(LEFT(A1)="0",A1,"0"&A1)
copy down as far as required.
Then copy this new range of cells, and Paste SpecialValues back over
the original.

--
Regards

Roger Govier


"bollard" wrote in message
...
Hello

In a spreadsheet containing UK phone numbers, all numbers should begin
with
a zero, but when we pull off a report and copy and paste it into
Excel,
depending on what's been input at source, some of the entries lack the
zero.

Is there a way of formatting the column so that, if no zero appears at
the
start of a phone number, one can be inserted at the start of each
number?

Thanks.

Inserting a "0"
 

Hi Roger

Many thanks for this.

It works a treat!

"Roger Govier" wrote:

Hi

If you format the receiving cells as Text before copying and pasting,
then the leading zero will be retained.

If you already have entries and some do not have leading zero's then you
could enter in a spare column
=IF(LEFT(A1)="0",A1,"0"&A1)
copy down as far as required.
Then copy this new range of cells, and Paste SpecialValues back over
the original.

--
Regards

Roger Govier


"bollard" wrote in message
...
Hello

In a spreadsheet containing UK phone numbers, all numbers should begin
with
a zero, but when we pull off a report and copy and paste it into
Excel,
depending on what's been input at source, some of the entries lack the
zero.

Is there a way of formatting the column so that, if no zero appears at
the
start of a phone number, one can be inserted at the start of each
number?

Thanks.

Inserting a "0"
 

Hello

Many thanks for that.

it works a treat.

"Edward" wrote:

On May 8, 8:00 am, bollard wrote:
Hello

In a spreadsheet containing UK phone numbers, all numbers should begin with
a zero, but when we pull off a report and copy and paste it into Excel,
depending on what's been input at source, some of the entries lack the zero.

Is there a way of formatting the column so that, if no zero appears at the
start of a phone number, one can be inserted at the start of each number?

Thanks.

If your number is of fixed length (say xxxx-xxxx) you can format like
the following:

Format-Cells-Number-Custom-0000-0000

This will turn the number 1234567 into 0123-4567.