how do i covert 9780764311223 to read 987-0-7643-1122-3
 

I have a column 0f 3500 of numbers to convert

how do i covert 9780764311223 to read 987-0-7643-1122-3
 

Custom format 000-0-0000-0000-0, or formula =TEXT(A1,"000-0-0000-0000-0")
--
David Biddulph

"ian78" wrote in message
...
I have a column 0f 3500 of numbers to convert

how do i covert 9780764311223 to read 987-0-7643-1122-3
 

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


"ian78" wrote:

I have a column 0f 3500 of numbers to convert

how do i covert 9780764311223 to read 987-0-7643-1122-3
 

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.

ian78 wrote:

I have a column 0f 3500 of numbers to convert

--

Dave Peterson

how do i covert 9780764311223 to read 987-0-7643-1122-3
 

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)

"ian78" wrote:

I have a column 0f 3500 of numbers to convert