fixing phone list

J

jkc42

Thanks again for all your help yesterday. I went to input all my phon
numbers and came up with a new problem I hope you can help with.
I have the numbers in the correct format now eg. 555555555 the proble
is that I now need to add a 1 in front of these to show the fax machin
that it is long distance. I need it to look like this now 1555555555
can this be done?? If so can you walk me through it?? I sure appreciat
any help or advise you can provide.

Thanks
Jod
 
C

cincode5

You can format the column to do this. Select all the cells you want to format then go to Format, Cells, select the Numbers Tab, then under Catagories select Custom. Now type the format in the Type field to look like this 1 ######### This should format all the phone number from 555555555 to 1555555555.
 
D

David McRitchie

Hi Jody,
You have created a new thread for continuing same question,
you'd probably have been better of continuing there. I don't
know whether you actually have text or actually have numbers,
or have formulas,

nor do I know if you used a macro to convert to numbers or a
formula with a helper column, or for that matter which you really want.

The following formula in a new column would prefix a 1 in
front of what it sees, the result will be text characters
returned from a formula.
="1" & F20 --- text

The following would assume you have 10 digit numbers
place 10000000000 in a cell and copy it (ctrl+C),
select the range of numbers you want to change
Paste Special, add
 
A

AlfD

Hi!

Might need to be careful with this. There is a difference between th
underlying number in the cell and some of the formats which show o
screen or in print.

Formatting as 1######### will look right on screen etc. It doesn'
however change the cell's contents.

If you want to change the contents:

Assume a phone no. is in A1
Add a spare ("helper") column B
Set B1 ="1" & A1
Copy down as necessary.

You can get rid of the helper column:
Copy col B
Paste special on A1 using values option
Delete col B

Al
 
D

David McRitchie

That's probably a better temporary solution, because I don't think this is going
to be the end of this problem. (Much as i dislike treating any id as a number).
but there is no space is what was asked for so you want the format to be
"1"0000000000
and whenever you assign another cell to the value you will not see the 1.

Keep in mind that numbers are limited to 15 digits of precision you
are currently using 10 and have a prefix in front.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

cincode5 said:
You can format the column to do this. Select all the cells you want to format then go to Format, Cells, select the Numbers Tab,
Click to expand...
then under Catagories select Custom. Now type the format in the Type field to look like this 1 ######### This should format all the
phone number from 555555555 to 1555555555.
 
Top