Help me with Excel, please.

January 16, 2008 7:13 PM

Can someone help me come up with an Excel formula? I have a column, and each row in the column has a 9 or 10 digit number. I want to apply a formula that will recognize only those numbers with 9 digits and when one is discovered, it should place a "0" in front of the number to make it a 10-digit number. I will explain why inside.

I am exporting a LibraryThing catalog into Excel as a CSV. In the ISBN column, all ISBNs that begin with a zero lose the zero after it is imported because Excel will not recognize numbers as text until I tell it to, but I have to open the spreadsheet first, and by that time, the damage is done. I have lost my zeroes. I do not want to have to go through it and insert the zero manually on each number. I know how to make the zero stay there after I have re-inserted it, but how can I get the zeroes to re-appear without manually doing it? There are hundreds of ISBNs.

posted by foxinthesnow to technology (7 comments total)

This doesn't answer your question, but it will allow you to import that data intact.

If you have a blank workbook open, on the Data menu, select Import External Data, then Import Data.

Find your file, then click Open. Pick "Delimited," if it's a CSV file. Click next, then uncheck Tab and check comma. Check "treat consecutive delimiters as one." Click next.

On this page, select the offending column and pick "text." Click finish, and you should be good.
posted by "Tex" Connor and the Wily Roundup Boys at 7:21 PM on January 16, 2008

I think you can open the spreadsheet, select the column, hit "Format Cells", go down until you get to the "Custom" Category, type ten zeros into the box, click OK and it should put a leading zero on any nine-digit number you have in there.
posted by jessamyn at 7:22 PM on January 16, 2008 [2 favorites]

Also, your zeros aren't "lost" per se, Excel just doens't know that it's supposed to show them to you. So basically what you're doing is telling Excel "I want to see ten digit numbers in this column. Make it so." Unless I am misunderstanding something?
posted by jessamyn at 7:23 PM on January 16, 2008

Aha! Jessamyn! You are right! I knew I had done it in the past, and that was exactly how. Thanks!
posted by foxinthesnow at 7:23 PM on January 16, 2008

You say that you import the CSV file into Excel, but do you mean that you open it or actually import it?

When importing a CSV file, Excel gives you the chance to specify that the field is text and not numeric. (It does this right after it asks for the delimiter.) This will preserve the leading zeros.

(I just tested this on Excel 2007 and I remember it being exactly like this on versions of Excel going back to Office 97.)
posted by oddman at 7:25 PM on January 16, 2008

If you do actually want them as text for some reason,

=right("000000000"&a1,10)

should do the job.
posted by flabdablet at 7:26 PM on January 16, 2008

I had the same problem with Excel losing the leading zero on my ISBNs, and Jessamyn's solution is now part of any ISBN work I do in Excel.
posted by cosmicbandito at 7:35 AM on January 17, 2008