ISBN #'s starting w/ "0" are deleted, how to format so "0" stays put

[Jessica]

Hello,
I need to know how to format a list of cells that contain
ISBN #'s so that when an ISBN starting with a "0" the zero
stays. I am working w/ Excel 97. I have tried to format
the cell to "Text" but then the zeros are deleted.

What I am doing is copying and pasting from another
spreadsheet. So, the ISBN looks like this at first:
0-889658-27-8
Then I say "Replace "-" with nothing. So then I get:
889658278
So, what I want is:
0889658278

Got me?
Any help would be greatly appreciated!

Thanks!

 

[Jason Morin]

I suggest formatting the cells as text *prior* to pasting
in the data or just this formula to add the 0:

="0"&A1

HTH
Jason
Atlanta, GA

 

[Guest]

Jason,
Thx. I did try to format the cells as text before copy and
pasting the column over as well as after wards I would
make sure that it was formatted as text.
I don't understand where to put that formula you
suggested?
So, I let it do the "889658278" and put that formula where?
Thanks for the help,
Jess

 

[Jason Morin]

Once you pasted in the data, insert the formula and update
the cell reference in the formula. For example, if the
first ISBN falls into B1, change the formula to ="0"&B1
and insert it into an open column in row 1. Then copy the
formula straight down the column to convert all the other
ISBNs.

Jason

 

[Jessica]

Okay,
So, I've decided that what I need to do is format the
cells so that the cell must have 10 numbers in it and if
there are not 10 numbers then it should insert zeros in
the front. How would I do that?
Jess

 

[Bernard Liengme]

Custom format 0000000000
best wishes

Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

 

[Guest]

Jessica
What Jason is suggesting is to use a helper column. However if you have some numbers that didn't start with a zero then his formula will still add a zero. Heres another way to use a helper column

1) Insert a column after your data, or use the existing column if it is blank

2) Assuming your data starts in A1, enter this formula in B1
=SUBSTITUTE(A1,"-",""

3) Fill down to bottom of data. If your data doesn't skip any rows you can do this very quickly by double clicking on the fill handle when B1 is the active cell. This is the black square in the lower right corner of the selected cell

4) Now you should have what you need in Column B. So select column B and Edit>Cop

5) Select Column A and Edit>Paste_Special>Values

6) Delete column

And yet another totally different way to get your numbers displayed as you want is with a custom number format. After you do your replace, select the column, Format>Cells>Number>Custom and enter 0000000000 in the Type field. this is probably the quickest way to get the display that you want but could cause problems if ou are doing a lookup or search on the data

Good Luck
Mark Graesse
(e-mail address removed)


----- (e-mail address removed) wrote: ----

Jason
Thx. I did try to format the cells as text before copy and
pasting the column over as well as after wards I would
make sure that it was formatted as text.
I don't understand where to put that formula you
suggested?
So, I let it do the "889658278" and put that formula where
Thanks for the help
Jes

 

[Guest]

If you don't want the dashes, do the following
Select the cells you want to forma
Go to Format-->Cell
Choose Custo
In the line where you can type put 000000000
(that's 10 consecutive zeroes

If you do wnat the dashe
Select the cells you want to forma
Go to Format-->Cell
Choose Custo
In the line where you can type put 0-000000-00-

Either way, after you have created the Custom format, you should be able to select it from the Format-->Cells-->Custom Box. It will be in the list of choices you can click on. If you type the dashes in--like in the second example above--you do not need to type them when you type the number into the cell.

 

[Jessica]

Jason,
I figured it out. I used the custom format of "0#########"
and now it works wonderfully. Thanks for your help.
Jess