Inserting a zero

J

Janice

Hi all,
I am working on a spreadsheet that as a column telephone numbers, however,
the '0' at the beginning of every number has been dropped.
How can I insert this so every number begins with a '0' ( the std's of the
numbers are from all over the UK so vary greatly)
Many thanks to anyone that can help me.
 
S

Steve Latronica Jr.

Hi all,
I am working on a spreadsheet that as a column telephone numbers, however,
the '0' at the beginning of every number has been dropped.
How can I insert this so every number begins with a '0' ( the std's of the
numbers are from all over the UK so vary greatly)
Many thanks to anyone that can help me.
Click to expand...

Use an apostrophe before the numbers, eg. '0123456

You may also choose to format the cell as text.
 
B

Bob I

Format as Text before entering the numeric characters, or precede them
with an ' (apostrophe)
 
J

Janice

--
Janice


Steve Latronica Jr. said:
Use an apostrophe before the numbers, eg. '0123456

You may also choose to format the cell as text.
Click to expand...

Hi, many thanks for that but is there anyway I can do this on mass - I have
over 2000 numbers that I need to do this on and I am hoping I dont have to do
them individually ! Thanks again
 
D

David Biddulph

Either
Format the cells as text before you type in the number
or
Precede the number with an apostrophe
or
Custom Format as 00000000000 if you know that all the numbers will be 11
digits
or
Use a helper column of ="0"&A2
 
P

Pete_UK

If you don't have any international calls (where the prefix should be
00), you can use a formula like this in a helper column:

=IF(LEN(A1)>6,"0"&A1,""&A1)

and copy this down. This retains the correct numbers for DQ and other
special services (like 118118, 123, 154 etc), and assumes that your
numbers start in A1. If you like, you can fix the values in the helper
column and then copy/paste them over the originals.

Hope this helps.

Pete
 
Top