I
ian78
I have a column 0f 3500 of numbers to convert
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
David Biddulph
Custom format 000-0-0000-0000-0, or formula =TEXT(A1,"000-0-0000-0000-0")
D
Duke Carey
2 ways -
First - make a backup copy of your sheet
If you want to leave it as it is but DISPLAY it differently, use
Format-Cells-Number, choose Custom from the left panel and under Type: enter
this code
000-0-0000-0000-0
If you actually need to change the value, in a column near the list (let's
assume the list is in column A) enter this formula along side of each entry
=text(A1,"000-0-0000-0000-0")
Copy the range of cells with the formulas, then select your original values
and use Edit-Paste Special - Values to replace the original data with the new
data, then clear all you formulas
First - make a backup copy of your sheet
If you want to leave it as it is but DISPLAY it differently, use
Format-Cells-Number, choose Custom from the left panel and under Type: enter
this code
000-0-0000-0000-0
If you actually need to change the value, in a column near the list (let's
assume the list is in column A) enter this formula along side of each entry
=text(A1,"000-0-0000-0000-0")
Copy the range of cells with the formulas, then select your original values
and use Edit-Paste Special - Values to replace the original data with the new
data, then clear all you formulas
D
Dave Peterson
Maybe just using format|cells|Number tab|custom would be enough.
Give it a format of:
000-0-0000-0000-0
This changes the display--not the value.
If you need to change the value, you could use a helper column of cells:
=text(a1,"000-0-0000-0000-0")
copy down the column
and edit|copy, edit|paste special|values over the original range. Then delete
the helper column.
ps. It's better to write your question in the body of the message--not just the
subject line.
Give it a format of:
000-0-0000-0000-0
This changes the display--not the value.
If you need to change the value, you could use a helper column of cells:
=text(a1,"000-0-0000-0000-0")
copy down the column
and edit|copy, edit|paste special|values over the original range. Then delete
the helper column.
ps. It's better to write your question in the body of the message--not just the
subject line.
B
bj
if they are really numbers format-cell-numbers-custom
000-0-0000-0000-0
if they are text that looks like numbers
=left(A1,3)&"-"&mid(a1,4,1)&"-"&mid(A1,5,4)&"-"&mid(A1,9,4)&"-"&mid(a1,13,1)
000-0-0000-0000-0
if they are text that looks like numbers
=left(A1,3)&"-"&mid(a1,4,1)&"-"&mid(A1,5,4)&"-"&mid(A1,9,4)&"-"&mid(a1,13,1)