I have a worksheet containing a column of cells each containing a mobile
phone number - the number in the cell is 7 digit , but needs to be 8 digit
(commencing with a "0" - which is now missing ) I need to insert a 0 at the
front of current string of 7 digits in each cell - I've done this before
but have forgot how to do. Can you pls tell me what formula / instruction I
must use to get a column with desired result - I need the phone number in
correct 8 digit format for a SMS transmission programme.
Many Thanks
Tom

Hi Tom,

To add a digit to the front of a number in each cell in an Excel column, you can use the CONCATENATE function or the ampersand (&) symbol.

Here are the steps to add a "0" to the front of a 7-digit number in each cell in an Excel column:

1. Insert a new column next to the column containing the mobile phone numbers.
2. In the first cell of the new column, type "0" (without the quotes).
3. Copy the "0" cell by selecting it and pressing Ctrl+C.
4. Select the entire new column by clicking on the column header.
5. Right-click on the selected column and choose "Paste Special" from the context menu.
6. In the "Paste Special" dialog box, choose "Values" and click "OK". This will paste the "0" into each cell in the new column.
7. Select the first cell in the new column that contains the "0".
8. Type the following formula:
   Formula:

   =CONCATENATE(A1) 

   or
   Formula:

   =A1&"" 

   (where A1 is the cell containing the 7-digit phone number).
9. Press Enter to apply the formula to the first cell in the new column.
10. Double-click on the small square in the bottom right corner of the cell to copy the formula down to all the cells in the new column.

This should add a "0" to the front of each 7-digit phone number in the original column, resulting in an 8-digit phone number in the new column.

"Killarney Tom" wrote:
I need to insert a 0 at the
front of current string of 7 digits in each cell

If the phone numbers are entered as numbers, not text, and you merely want
the cell value to appear as 8 digits with leading zeros, you can use the
Custom format 00000000.

If the phone numbers are text or numbers, and you want want the actual cell
value to be 8 digits, use the following to create 8-digit text:

=TEXT(A1,"00000000")

where A1 contains the original phone number. If you want to replace A1 with
the result of the formula (in B1, say), copy B1 and use paste-special-value
to overwrite A1.


----- original message -----

"Killarney Tom" wrote:
I have a worksheet containing a column of cells each containing a mobile
phone number - the number in the cell is 7 digit , but needs to be 8 digit
(commencing with a "0" - which is now missing ) I need to insert a 0 at the
front of current string of 7 digits in each cell - I've done this before
but have forgot how to do. Can you pls tell me what formula / instruction I
must use to get a column with desired result - I need the phone number in
correct 8 digit format for a SMS transmission programme.
Many Thanks
Tom