To fix this,
1.) Open the file in Excel and insert 2 columns after column A (which is the column for your telephone number).
2.) Change the format of the first column you inserted(column B) to 'scentific' (Right-click on the heading of the column and select 'scentific' as format).
3.) Enter the following formula to add the '0' in front of the telephone number as show in column A:
- Code: Select all
=CONCATENATE(0,A1)
It will only allow you to add the formula if in scentific format.
4.) Change the format of Column B to 'text'.
5.) Now copy the formula in B1 to all Cells in Column B, thus B2 until end of list for all entries as displayed in Column A . Now column B should have all entries of Column A with a leading zero.
6.) You need to copy Column B and 'Special Paste' the 'Values' to Column C. We need the value of the CONCATENATE function and not the formula, as the formula refers to a specific cell to get the value and we need to remove the 2 columns again.
7.) If Column C is identical to Column B, then you may delete Column A and B.