adding zero at the start of number

[vipulgos]

Dear all,
I have phone numbers imported from outlook in csv format. Now these numbers are like 2782512512, i want to add Zero at the start of this number. So, it will become 02782512512. How can i do this. Find and Replace method doesn't work.
please guide me.

[Bjornar]

Your numbers are probably stores as numbers, if you still want them stored as numbers you could use custom number formatting.
Select Format -> Cell -> Number -> Custom
and enter this format: 00000000000
this makes your number display with 11 digits and leading zeros.

If you want your numbers stored as text. With your numbers in column A, but this inn B1 and copy down:
=TEXT(A1,"00000000000")
or this
="0"&A1

the latest formula even works for number with less or more than 10 digits after the zero, and is probably your best choice.

You can the use copy and paste special values to convert eliminate the formulas, and then copy column B to column A, to replace the original number.

[vipulgos]

But in both case:
Few numbers are already started with 0278, 50% of number starts with 278. So, applying format or formula to the numbers which starts from 278 will then have to re-enter in the B column by picking one by one cells. This is a real headache.
Also this problem is for business phone numbers, home phone numbers, etc. So, applying formula to all is not possible. I require something like find 278 and replace it with 0278. I think this is only the remedy

[Bjornar]

I still think we can find a formula that works for you.
Could you post some representative examples of some of the numbers you are trying to convert, and what result you want for each of these.
Then I will try to come up with a formula.

[vipulgos]

Hello dear this is the data you want to see. These data after compilation i want to send it back to outlook and then to my mobile device.
******** language="JavaScript" ************************************************************************>

Microsoft Excel - for mobile-4.csv ___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Middle Name	Last Name	Suffix	Company	Job Title	Business Street	Business City	Home Street	Business Fax	Business Phone	Business Phone 2	Home Phone	Home Phone 2	Mobile Phone
2	D.	Amin							9426345134	2782432139		2782423554		9824740820
3	D.	Gohil-IOB							a-bad-26582356	079-26587390 /91-padmanabhan		2782525740		9426920907
4	P.	Bhatt										2782203619		9898982902
5										2782421029		002782445577 /2449138-jagdish jajadia		9426819168 /9898985577-jagdish jajadia
6					Ad.				2782519883	+091 (00278) 2424322	2782424322	2782567818	278560771	9824209330 hiteshbhai
7	T.	Rathod												9825803808
8	Bhangari	Scrap								pp-2427042				9825207127
9		Gosalia										9428993622		9824286243
10		Aastikbhai												9427755322
11		inquiry								66666666
12										2782413211				9328930959
13									9825505089 -nilaben	2540218 , 2540232		002782571876 -thakur		9879042481 -ashok josi
14							oil-7300-5/8/06			2782511934
15										2782431367		2782570686		9825082057
16									9327322272 -aharisaheb	2782562509		02842-236001 -alang		9377422272 -son
17		Ahemdabad civil												9.17922E+11
18		Duccan								2783091179				batibhai-9426941459
19						204, Toran Apts., Asopalav society, Tadvadi, Rander road,	Surat			2782414721		0261-6556615 /2567280		9825045360
20		Shah										2782201098		9426461302
21		Ajay Shukla												9824221168
22	Vora	Uiic												9825553052
23					GSFC				94272002780 --ajaybhai			27825602787		9426256487 - daughter
24		Sheth												9426210444
25					Er.					9374524848		2782567606		9426968011
26		Thakker							9322107123			2782566309		9426207123
27		Section-Vishwakarma								2782520751		2471065 /2541007
28														9909606060
for mobile-4

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[Domenic]

When importing the file into Excel, format the relevant columns as 'Text'. You're given an opportunity to do so, probably during Step 3, where you can click to choose your column or columns. Then select 'Text' as your format.

Hope this helps!

[vipulgos]

But the data in outlook itself starts from 278, in place of 0278. That's why i want to import to excel, so that i can edit in excel (278 to 0278).
After editing I'll send these data back to outlook and then mobile device

[Domenic]

For clarity, can you post a small sample of the data as it starts out, the way you'd like it to look, and the rules involved.

Note: If the data you've already posted is real data, I'd suggest you remove it (or if you're not able, ask one of the moderators to have it removed) and post fake data instead.

[vipulgos]

The data I've posted is the real data and there is nothing secret about it. so, please be continue.
I want the numbers which starts like 278222222 are to be edited as 0278222222. Also, it should be remain in number or general format. So that while exporting these data back to outlook, it should be go back to 278222222,
thanks

[Bjornar]

Try this in Q2:

=IF(LEFT(K2;3)="278";"0"&K2;K2)

and copy to the range: Q2 to V30
This puts a zero in front af all numbers starting with 278, and leaves all other untouched.