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.

 

[Domenic]

Does this return the desired result in all cases?

Q2, copied down:

=TEXT(K2,REPT(0,INDEX({4,10},MATCH(LEN(A2),{0,9}))))

 

[francisp]

Hi Vipul,

Just enter the below formula in the adjacent columns i.e if Phone numbers are in Column A, enter the formula in Column B. You will get the phone numbers in the format you want.

=IF(LEFT(A2,1)="0",A2,"0"&A2)

 

[vipulgos]

All the formula sent are no doubt good, but what happen next is the real problem. After getting all the data edited, this workbook is to be save in csv format. At this time saving in csv format, all the editing will be lost. The data will once more looks like 2782512222 in place of 02782512222. I wonder why other members haven't face the same problem?

 

[vipulgos]

Members please come forward, I want to transfer outlook data to my new phone. It's really urgent.

 

[Bjornar]

As long as you make sure your numbers are formatted as text, the leading zeroes should be preserved. I just tested this myself. Formatted some cells text, and added leading zeros to some numbers. Then saved the woorksheet as .csv file.
Open the file in Notepad, and you will see the zeros are still there. If you try to open the .csv file in Excel all will be converted back to numbers and the zeroes will dissapear. So I don't think the problem is in saving the .csv file. The problem is in the reopening/import you do in Outlook or wherever you try to open the file. I have not tried to import to Outlook, so I don't know if there are any option for specifying that numbers should be imported as text.

 

[vipulgos]

As long as you make sure your numbers are formatted as text, the leading zeroes should be preserved. I just tested this myself. Formatted some cells text, and added leading zeros to some numbers. Then saved the woorksheet as .csv file.
Open the file in Notepad, and you will see the zeros are still there. If you try to open the .csv file in Excel all will be converted back to numbers and the zeroes will dissapear. So I don't think the problem is in saving the .csv file. The problem is in the reopening/import you do in Outlook or wherever you try to open the file. I have not tried to import to Outlook, so I don't know if there are any option for specifying that numbers should be imported as text.

please try to save the file after formating numbers to text starts from zero.When you save the same file with csv, it will re-formated as number without zero in front. I request you to please try to import the data in outlook, you will realise the problem.

 

[Bjornar]

I just tried importing a .csv file to outlook contacts. The zeroes in front of phone number came thru, no problem.

Did you try to open your .csv file in Notepad ? You need to tell me if the zeros are missing there or not. Then we can figure out if the problem is in Excel or in Outlook.

If the zeroes are not in the .csv when you open it with Notepad, the problem is in Excel. If so, make sure you select all your cells. Format as text, and make sure you see the zeroes. Then it should be exported correct.

 

[vipulgos]

Dear all,
I found the solution,
1) export data in Microsoft Excel ( not as csv)
2) go to edit>findalll>278
3) select all the cells in find dialog box, then come to excel sheet, don't left click anywhere, just right click in the sheet>the apply format as TEXT. then replace all with 0278.
Then save it close it.
Don't try to change any field name, etc.
Then import this back to outlook. If any field removed or edited, it will give Translation error....ODBC error, etc., on import choose the default mapping.
This will solve the problem.