Inserting a "0"

[Guest]

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.

 

[Edward]

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.

 

[Roger Govier]

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 Special>Values back over
the original.

 

[Guest]

Hi Roger

Many thanks for this.

It works a treat!

 

[Guest]

Hello

Many thanks for that.

it works a treat.

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.