How to put the number 0 in front of telephone numbers please?

[mobilewebpage]

Hi

I have my database but for some reason on this particular sheet it has left out the number 0 in from of each number

So the list looks like this (These are examples)

7000228478
7017012740
7017400845

How can i get a 0 put on front of all the numbers in the list

Thanks people

:-)

[XOR LX]

Assuming they're in cells A1:A3, put this in B1 and copy down: =0&A1

Regards

[Lemice]

Hello,

If you want to copy paste / type a phone number with at least one 0 in front of a number, you will have to format the entire column as "Text" (Which can be done through selecting the whole column, Ctrl-1 and select Text).

If you want to convert existing number like in your example, assuming they are in column A starting from A1, you can use this formula
=0&A1
Paste it on B1 and drag it down

[oeldere]

format => last option => format as 00000000000 (11 zero's)

[mobilewebpage]

Hello,

If you want to copy paste / type a phone number with at least one 0 in front of a number, you will have to format the entire column as "Text" (Which can be done through selecting the whole column, Ctrl-1 and select Text).

If you want to convert existing number like in your example, assuming they are in column A starting from A1, you can use this formula
=0&A1
Paste it on B1 and drag it down

Hi

This seems to work for 1 number but i am not sure how to drag it down,

Any ideas please

[Marshy3300]

select the cells, press CTRL + 1, click Custom, in the box type 00000000000 (11 zeros- which is your 10 digit phone number plus the extra zero) then click OK.

[Kevin UK]

Hi mobilewebpage

There is no need to turn it into a text value, such as "0&A1"
As posts #4 & #6 format the cells as "00000000000" or if you want to separate the mobile code, format as "00000 000000" (without the quotes)

[mobilewebpage]

select the cells, press CTRL + 1, click Custom, in the box type 00000000000 (11 zeros- which is your 10 digit phone number plus the extra zero) then click OK.

Fantastic that worked wonders but when i save the excel spread sheet it says it is not compatable as a .csv file

I would like to save as a .csv file if possible?

Thanks people

[mobilewebpage]

I am going to open a new thread people as i want to explain the issue more

[oeldere]

in that case you could use a ' before the 11 zero's.

(in the box format => last option)

[mobilewebpage]

in that case you could use a ' before the 11 zero's.

(in the box format => last option)

That works and makes them like this

'07000228478
'07017012740
'07017400845
'07043542543
'07050383202

I need to put the .csv into some software but i dont think it will work with the ' in front if the 0's

[oeldere]

Did you tried it?

[icestationzbra]

@mobilewebpage,

no need for the 'apostrophe'.

format your data with the "Custom" format suggested elsewhere.

then, click on Save As.

in the "File Name" field, enter the desired file name with quotes around it, followed by the CSV extension - such as, "book1.csv".

now, click on Save button. the file should save without a whimper.

the next item is VERY important - DO NOT attempt to open the file in Excel - once you open it in Excel, the formatting will GO AWAY.

if you need to inspect the file, open it in Notepad or Wordpad instead.